Skip to content

Indexes

Data structure

  • Data structure used : B-Tree
  • Self Balancing Index
  • SELECT, INSERT, DELETE and sequential access in logarithmic time
  • Can be used for most of operations and column type
  • supports unique condition
  • Used in Primary Key
  • Used with operators
  • Used when pattern matching

Hash Index

https://codingsight.com/hash-index-understanding-hash-indexes/

  • for equality operators
  • not for range
  • Larger than btree in size

Brin Index

  • block range index
  • block data -> min to max value
  • smaller index
  • less costly to maintain than btree index
  • Can be used on very large table
create table t_big
(
    id   serial,
    name text
);

drop table t_big;

insert into t_big (name)
select 'adam'
from generate_series(1, 2000000);


CREATE INDEX CONCURRENTLY brin_index
    ON public.t_big USING brin
    (id);

create index btree_index on t_big(id);

select pg_size_pretty(pg_total_relation_size('t_big'));

select pg_size_pretty(pg_indexes_size('t_big'));

drop index brin_index;

drop index btree_index;

explain analyse
select *
from t_big
where id = 9999;

explain analyse
select id
from t_big
order by id desc limit 100;

Partial Index

  • To performance of the query while reducing the index size.
create index if not exists partial_inx on t_big(id) where id > 1000000;


explain analyse
select *
from t_big
where id = 10000033;


explain analyse
select *
from t_big
where id = 99999;

Expression Index

  • PostgreSQL will use this index when WHERE clause or ORDER BY clause in statement
  • Very Expensive to use
CREATE TABLE IF NOT EXISTS t_dates AS
SELECT d, repeat(md5(d::text),10) as padding
    FROM generate_series
        (timestamp '1800-01-01', timestamp '2100-01-01', interval '1 day') s(d);

select * from t_dates limit 10;

vacuum analyse t_dates;

EXPLAIN ANALYSE
SELECT *
FROM t_dates
WHERE d BETWEEN '2001-01-01' AND '2001-01-31';


CREATE INDEX IF NOT EXISTS idx_t_dates_d on t_dates (d);

ANALYSE t_dates;

EXPLAIN ANALYSE
SELECT *
FROM t_dates
WHERE d BETWEEN '2001-01-01' AND '2001-01-31';

EXPLAIN ANALYSE
SELECT *
FROM t_dates
WHERE EXTRACT(DAY FROM d) = 1;

CREATE INDEX idx_expr_t_dates on t_dates (extract(day from d));

ANALYSE t_dates;

EXPLAIN ANALYSE
SELECT *
FROM t_dates
WHERE EXTRACT(DAY FROM d) = 1;

Heap Index