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
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;