Hypothetical Indexes in PostgreSQL — Need and Usage

Need of Hypothetical Indexes :

  • Lets ignore the risk and make the index on live table which can result the following scenario
  • Another solution is to replicate the production database to the local dev environment and then apply all the hits and try there and then apply at the production environment . it seems a very safe and effective approach in almost all cases but this will take too much of time in setting up the things and testing with actual scenario .
  • Third Solution is Hypothetical Indexes as this functionality will create imaginary indexes not real indexes . But there are some things to note about these indexes :

Usage of Hypothetical indexes :

Installation

  • Download hypopg by following command :
Wget  https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/hypopg_10-1.1.4-1.rhel7.x86_64.rpm
  • Then install it on CentOS7 :
yum install hypopg_10-1.1.4-1.rhel7.x86_64.rpm
  • Now create extension using following query
testdb=# CREATE EXTENSION hypopg ;
CREATE EXTENSION
  • On Creating extension following functions will be created .
testdb=#  select proname from pg_proc where proname ilike '%hyp%';
proname
----------------------
hypopg_reset_index
hypopg_reset
hypopg_create_index
hypopg_drop_index
hypopg
hypopg_list_indexes
hypopg_relation_size
hypopg_get_indexdef

Usage

testdb=# create table orders as select s as orderno, md5(random()::text) as orderitem , now() as order_created from generate_Series(1,100000000) s;
SELECT 100000000
testdb=# explain select * from orders where orderno > 80000  order by order_created desc  limit 100 ;
QUERY PLAN
--------------------------------------------------------------------------------
Limit (cost=3600088.98..3600089.23 rows=100 width=44)
-> Sort (cost=3600088.98..3688095.27 rows=35202513 width=44)
Sort Key: order_created DESC
-> Seq Scan on orders (cost=0.00..2254674.25 rows=35202513 width=44)
Filter: (orderno > 80000)
(5 rows)
testdb=# SELECT * FROM hypopg_create_index('create index order_created_idx on orders(order_created)');
indexrelid | indexname
------------+-----------------------------------
24797 | <24797>btree_orders_order_created
(1 row)
testdb=# explain select * from orders where orderno > 80000  order by order_created desc  limit 100 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.07..4.29 rows=100 width=45)
-> Index Scan Backward using "<24797>btree_orders_order_created" on orders (cost=0.07..4215496.19 rows=99917459 width=45)
Filter: (orderno > 80000)
(3 rows)
testdb=# select * from hypopg_drop_index(24797);
hypopg_drop_index
-------------------
t
(1 row)
testdb=# SELECT * FROM hypopg_create_index('create index order_created_idx on orders(order_created)');
indexrelid | indexname
------------+-----------------------------------
24798 | <24798>btree_orders_order_created
(1 row)
testdb=# select * from pg_size_pretty(hypopg_relation_size(24798));
pg_size_pretty
----------------
2990 MB
(1 row)
testdb=# create index order_created_idx on orders(order_created);
CREATE INDEX
testdb=# \di+ order_created_idx
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------------+-------+----------+--------+---------+-------------
public | order_created_idx | index | postgres | orders | 2142 MB |
(1 row)

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store