Hypothetical index in Postgres

A hypothetical index in PostgreSQL is a special type of index that doesn't actually exist physically on disk but is "imagined" or "simulated" by PostgreSQL. This allows you to evaluate the potential benefits of an index without incurring the cost of creating it.

Hypothetical indexes are used primarily for performance tuning and query optimization. You can analyze how a query would behave if a particular index were to exist, helping you decide whether creating the index is worthwhile.

#Install the extension

CREATE EXTENSION hypopg ;

\dx

Create a hypothetical index

 CREATE TABLE hypo (id integer, val text) ;

INSERT INTO hypo SELECT i, 'line ' || i FROM generate_series(1, 100000) i ;

VACUUM ANALYZE hypo ;

EXPLAIN SELECT val FROM hypo WHERE id = 1;

SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)') ;

EXPLAIN SELECT val FROM hypo WHERE id = 1;

EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;

  

Manipulate hypothetical indexes

SELECT * FROM hypopg_list_indexes ;

SELECT * FROM hypopg() ;

SELECT indexname, hypopg_get_indexdef(indexrelid) FROM hypopg_list_indexes ;

SELECT hypopg_create_index('CREATE INDEX ON hypo (id, val text_pattern_ops)');

EXPLAIN SELECT * FROM hypo

 

EXPLAIN SELECT * FROM hypo

WHERE id < 1000 and val LIKE 'line 100000%';

 

How to do Index size estimation ?

SELECT indexname,pg_size_pretty(hypopg_relation_size(indexrelid))

FROM hypopg();

 

Test for size validation :

CREATE INDEX ON hypo (id);

CREATE INDEX ON hypo (id, val text_pattern_ops);

SELECT relname,pg_size_pretty(pg_relation_size(oid))

FROM pg_class WHERE relkind = 'i' AND relname LIKE '%hypo%';