Automatic indexes recommendations in PostgreSQL

In our last blog we learned about  Need and Usage of Hypothetical indexes in Postgresql  . Now we can check easily in live environment also if some particular index will be helpful or not , but  how we get to know which index to test  .  It requires in-depth knowledge of indexing and experience in Postgresql , But in PostgreSql we can get automatic recommendation of indexes for specific queries by using three extensions hypog,pg_stat_statements,pg_qualstats . 

Now Let’s move directly to the practical part how we can use  this feature in postgres  : 

i am doing experiments on Postgres10 installed on Centos7 .

Installation 

• Install hypog from  https://hello-worlds.in/2021/05/23/hypothetical-indexes-in-postgresql-need-and-usage/
• Install  pgqualstats using following command : 

yum install pg_qualstats10.x86_64

• Change the following in postgresql.conf and restart postgresql 

shared_preload_libraries = 'pg_stat_statements, pg_qualstats' 

• Create following extensions : 

testdb=# CREATE EXTENSION hypopg ;
CREATE EXTENSION
testdb=# CREATE EXTENSION pg_stat_statements ;
CREATE EXTENSION
testdb=# CREATE EXTENSION pg_qualstats;
CREATE EXTENSION

• Set sample rate of pgqual stats to 1 in postgresql.conf  . This rate define how frequently monitor  and analyze the queries . Value ‘1’ represents that keep track of all queries

pg_qualstats.sample_rate = '1'

• Create the function which will be used to detect usable indexes : 

CREATE OR REPLACE FUNCTION find_usable_indexes()
RETURNS VOID AS
$$
DECLARE
    l_queries     record;
    l_querytext     text;
    l_idx_def       text;
    l_bef_exp       text;
    l_after_exp     text;
    hypo_idx      record;
    l_attr        record;
    /* l_err       int; */
BEGIN
    CREATE TABLE IF NOT EXISTS public.idx_recommendations (queryid bigint, 
    query text, current_plan jsonb, recmnded_index text, hypo_plan jsonb);
    FOR l_queries IN
    SELECT t.relid, t.relname, t.queryid, t.attnames, t.attnums, 
    pg_qualstats_example_query(t.queryid) as query
      FROM 
        ( 
         SELECT qs.relid::regclass AS relname, qs.relid AS relid, qs.queryid, 
         string_agg(DISTINCT attnames.attnames,',') AS attnames, qs.attnums
         FROM pg_qualstats_all qs
         JOIN pg_qualstats q ON q.queryid = qs.queryid
         JOIN pg_stat_statements ps ON q.queryid = ps.queryid
         JOIN pg_amop amop ON amop.amopopr = qs.opno
         JOIN pg_am ON amop.amopmethod = pg_am.oid,
         LATERAL 
              ( 
               SELECT pg_attribute.attname AS attnames
               FROM pg_attribute
               JOIN unnest(qs.attnums) a(a) ON a.a = pg_attribute.attnum 
               AND pg_attribute.attrelid = qs.relid
               ORDER BY pg_attribute.attnum) attnames,     
         LATERAL unnest(qs.attnums) attnum(attnum)
               WHERE NOT 
               (
                EXISTS 
                      ( 
                       SELECT 1
                       FROM pg_index i
                       WHERE i.indrelid = qs.relid AND 
                       (arraycontains((i.indkey::integer[])[0:array_length(qs.attnums, 1) - 1], 
                        qs.attnums::integer[]) OR arraycontains(qs.attnums::integer[], 
                        (i.indkey::integer[])[0:array_length(i.indkey, 1) + 1]) AND i.indisunique)))
                       GROUP BY qs.relid, qs.queryid, qs.qualnodeid, qs.attnums) t
                       GROUP BY t.relid, t.relname, t.queryid, t.attnames, t.attnums                   
    LOOP
        /* RAISE NOTICE '% : is queryid',l_queries.queryid; */
        execute 'explain (FORMAT JSON) '||l_queries.query INTO l_bef_exp;
        execute 'select hypopg_reset()';
        execute 'SELECT indexrelid,indexname FROM hypopg_create_index(''CREATE INDEX on '||l_queries.relname||'('||l_queries.attnames||')'')' INTO hypo_idx;      
        execute 'explain (FORMAT JSON) '||l_queries.query INTO l_after_exp;
        execute 'select hypopg_get_indexdef('||hypo_idx.indexrelid||')' INTO l_idx_def;
        INSERT INTO public.idx_recommendations (queryid,query,current_plan,recmnded_index,hypo_plan) 
        VALUES (l_queries.queryid,l_querytext,l_bef_exp::jsonb,l_idx_def,l_after_exp::jsonb);        
    END LOOP;    
        execute 'select hypopg_reset()';
END;
$$ LANGUAGE plpgsql;

Usage 

• Now Let’s make a table with 10 Crores rows as following : 

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

• Now lets run a query on which we want to check if need to make any index  

testdb=# select * from orders where orderno = 80000  ;
-[ RECORD 1 ]-+---------------------------------
orderno       | 80000
orderitem     | 03b41c2f32d99e9a597010608946c4c6
order_created | 2021-05-22 17:52:21.435936+05:30

• Now run following queries to find out  which indexes are recommend by this extension and what are the improvement percentage by applying these indexes hypothetically 

testdb=#           select find_usable_indexes();
 find_usable_indexes 
---------------------
 
(1 row)


testdb=# select b.query, a.recmnded_index,round((((a.current_plan->0->'Plan'->>'Total Cost')::numeric-(hypo_plan->0->'Plan'->>'Total Cost')::numeric)*100/(a.current_plan->0->'Plan'->>'Total Cost')::numeric),2) as percent_improvd FROM idx_recommendations a JOIN pg_stat_statements b ON a.queryid = b.queryid WHERE round((((current_plan->0->'Plan'->>'Total Cost')::numeric-(hypo_plan->0->'Plan'->>'Total Cost')::numeric)*100/(current_plan->0->'Plan'->>'Total Cost')::numeric),2) > 0 order by 3 desc ;
                        query                        |                          recmnded_index                          | percent_improvd 
-----------------------------------------------------+------------------------------------------------------------------+-----------------
 select * from orders where orderno = $1             | CREATE INDEX ON public.orders USING btree (orderno)              |          100.00

Above  analysis was internally done by creating the indexes hypothetically not by making real indexes .

Please Note here that do not fully rely on automatic index recommendation . Yes we have no doubt it is very very useful feature  , but please also check logically why these recommended indexes are useful and should you really create it or not. 

You can read PostgreSQL Index Tutorial Series for basic in depth understanding of indexes in postgresql . 

Now , You can try the explained feature with more complex queries and comment on this article explaining your result  with your queries . So let’s experiment and comment . 

Refrences : https://www.percona.com/blog/2019/07/22/automatic-index-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/

Advertisement

Find tables on which Postgresql doing Sequential Scan

First let’s understand why we should find out the tables for Sequential Scan. Basically what happens is when we design the schema and create indexes, we want that almost in every case the system should use indexes for getting the data, but later somebody writes a query which dosen’t use indexes and do sequential scan and slows down your system at scale.

To solve this part we should first find out on what tables Postgresql is doing sequential scan. For this postgresql provides stats.

Lets do a query on the stats to find tables for sequential scan:

SELECT schemaname, relname,seq_scan, seq_tup_read,seq_tup_read / seq_scan as avg_seq_tup_read FROM pg_stat_all_tables WHERE seq_scan > 0 ORDER BY 5 DESC LIMIT 5;

schemaname | relname  | seq_scan | seq_tup_read | avg_seq_tup_read 
------------+----------+----------+--------------+------------------
 public     | tickets  |        3 |     24000000 |          8000000
 public     | orders5  |       11 |     50000000 |          4545454
 public     | student  |       18 |      5600010 |           311111
 pg_catalog | pg_class |    89061 |     27452103 |              308
 pg_catalog | pg_index |       35 |         1174 |               33
(5 rows)

The above query tells on which tables sequential scan is done , seq_scan provides the count of sequential scan , seq_tup_read tells the total no. of rows read by the system in all the sequential scan and avg_seq_tup_read tells on average how much rows is read by the query.

Now lets see how to figure out whether this is fine or not:

• First list all the tables on which sequential scan should not be running – in our case lets take tickets tables
• Now get the total no. of records in tickets table. – in our case it is – 8000000
• Now check whether system is scanning the entire table or large portion of the table every time
  • as we can see that avg_seq_tup_read is also 8000000 which means system is scanning entire table every time
• If the system scanning entire table and you know this table would be huge in future that means some queries on this table are actually not using the index – which is not good
• check from postgresql logs – the queries on this table which take time (there may be a case you need to enable all query logs for postgresql for some time — generally do not enable this for long , might cause downtime in production , a ok setting would be log any query which is greater than 1 sec)
• Then check using explain analyze whether this query is doing sequential scan – (if in doubt do not run in production system or run in very low load hours)
• After you discovered the query try to create indexes – we have a blogs on understanding indexes and why system not using your index , you can check out these if you are unable to figure out.

Why Postgresql Index not working (Bitmap vs Index) ? – Part 2

In last blog series we learned basics of Indexing. In this blog series part 1 we worked on why query is not using the index. Basically sometimes what happens is, when we run a query we thought it should use a particular index but it actually either use diff. index or do not use index at all and do sequential scan. So in this blogs series we will work on how postgresql choose index.

When we ask Postgres to execute a query it is executed in three steps:

• Parse – to validate the query sytax
• Planner – to plan how the query is executed like whether to do index scan or sequential scan or bitmap scan
• Executor – it will finally execute the planned tasks

Here we are more concerned about the Planner part, the entire purpose why the query needs planning is to reduce the total I/O (reading from disks/memory).

In last part we will work on example where Postgres use the index on the order by column and why it does that.

In this part we will work on why Postgres is doing Bitmap Scan rather than Sequential Scan.

First Lets understand a bit about Index Scan and Bitmap Scan:

• Index Scan
  • Postgres scan the index first and then go to the table and scan the table
  • In this it does random scan of the table row by row
  • It is faster when the index data is sorted in a way which is required by the query.
• Bitmap Scan
  • Postgres scan the index completely and mark all the pages which contains a matching value rather than checking the rows
  • After completed the index scan (mostly entire index) and have list of all the pages which contains the data
  • It then scan all the tables pages (marked one) and again check the validation conditions in the rows.
  • It is chosen over index when cost of random scan with index is higher.

How we can get a hint whether Index scan is preferred or Bitmap Scan

• This is based on the correlation between the order in data and its physical representation(in filesystem)
• Lets understand it by example.
  • First create a table:
    • create table orders as select s as orderno , md5(random()::text) as orderitem , now() as order_created from generate_Series(1,1000000) s;
  • You can we we create a table with a generated series 1-1000000 that is generated sequentially and dumped in the same order as well.
  • Postgresql calculate certain statistics about the column order (increasing or decreasing) with table physical order increasing or decreasing and this data can be see using the following query:
    • First analyze the table : ANALYZE orders;
    • Now run: SELECT attname, correlation FROM pg_stats WHERE tablename = ‘orders1’;
    • you will see correlation for orderno column is 1
    • and this correlation varies from -1 to 1 – desc to asc.
• When the correlation is high Postgres prefers Index scan but when correlation is less postgresql prefer Bitmap.

Lets create a case where Index Scan will be preferred:

First create table and indexes:

create table orders as select s as orderno  , md5(random()::text) as orderitem , now() as order_created from generate_Series(1,1000000) s;

create index orderno_idx on orders(orderno);

create index order_created_idx on orders(order_created);

Now lets check the correlation value :

SELECT attname, correlation FROM pg_stats WHERE tablename = 'orders';
    attname    | correlation 
---------------+-------------
 orderno       |           1
 orderitem     |  0.00561977
 order_created |           1
(3 rows)

As you can see correlation value is 1 , there is high chance Postgresql will use Index Scan:

Now lets query :

explain analyze select * from orders where orderno < 80000   limit 100 ;
                                                             QUERY PLAN             
                                                
------------------------------------------------------------------------------------
------------------------------------------------
 Limit  (cost=0.42..4.22 rows=100 width=45) (actual time=7.494..7.602 rows=100 loops
=1)
   ->  Index Scan using orderno_idx on orders  (cost=0.42..3028.31 rows=79879 width=
45) (actual time=7.492..7.579 rows=100 loops=1)
         Index Cond: (orderno < 80000)
 Planning time: 0.156 ms
 Execution time: 7.653 ms
(5 rows)

As we can see here the Postgres is using Index Scan.

Lets create a case where Bitmap Scan will be preferred:

First create table and indexes:

create table orders as select random()*10000 as orderno  , md5(random()::text) as orderitem , now() as order_created from generate_Series(1,10000000) s;

create index ord_idx on orders(orderno);

create index order_created_idx on orders(order_created);

Here one important change is rather than using s (generated series) we are generating a random number (random()*10000) such that correlation should be less.

Lets see the coorelation:

analyze orders;

SELECT attname, correlation FROM pg_stats WHERE tablename = 'orders5';
    attname    | correlation 
---------------+-------------
 orderno       |  0.00156549
 orderitem     | 0.000700192
 order_created |           1

Here we can see that the correlation is very less.

Now lets query the table:

explain analyze select * from orders where  orderno >9000;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on orders  (cost=19145.75..135021.51 rows=1022621 width=49) (actual time=105.159..1042.678 rows=1002305 loops=1)
   Recheck Cond: (orderno > '9000'::double precision)
   Rows Removed by Index Recheck: 5746548
   Heap Blocks: exact=37001 lossy=66089
   ->  Bitmap Index Scan on ord_idx  (cost=0.00..18890.09 rows=1022621 width=0) (actual time=99.446..99.447 rows=1002305 loops=1)
         Index Cond: (orderno > '9000'::double precision)
 Planning time: 0.158 ms
 Execution time: 1067.465 ms


explain analyze select * from orders where  orderno >0;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on orders5  (cost=0.00..228093.00 rows=10000000 width=49) (actual time=0.032..1013.239 rows=10000000 loops=1)
   Filter: (orderno > '0'::double precision)
 Planning time: 0.282 ms
 Execution time: 1257.460 ms
(4 rows)

Now we can see the system used Bitmap Scan.

Also one more important thing to remember here is that system will sometimes will switch to Sequential Scan also rather than Bitmap Scan when the number of rows selected are very high.

So guys in this tutorial we learned about how Postgres select index primarily Bitmap vs Index.

Hypothetical Indexes in PostgreSQL – Need and Usage

In this Blog We will first cover what are hypothetical indexes and what is need of these type of indexes and then secondly  we shall see the practical usage of these indexes . 

Need of Hypothetical Indexes : 

As the name suggests   these are not real indexes,  these are hypothetical indexes i.e.. They are virtual indexes which PostgreSQL  query planner does not consider when running queries . 

 Now the question arises where these Hypothetical Indexes  are useful  ? ? ? 

First Let’s discuss one scenario  , we have a large table which is currently in the production environment  and we need to make some index on live db and we are not sure whether that index will be useful or not , we even don’t know if  by making that index our production environment may be down !!!

So , Solution of above problem will be following : 

• Lets ignore the risk and make  the index on live table which can result the following scenario 
  • first of all it will take lots of time depending on data present in table 
  • Live queries may get affected badly if we are not sure if the index we are making will increase or decrease the cost .
  • We also do not know the size of the index it may be too large which can again impact the production database server . 
• 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 :
  •  it creates an index in our connection’s private memory. None of the catalog tables nor the original tables are actually touched. 
  • The only way to see if we can benefit from that index is by running an EXPLAIN <QUERY>.
  •  If you wish to run an EXPLAIN ANALYZE that runs that SQL and provides the run time stats, it would not be able to use that hypothetical index. This is because a hypothetical index does not exist in reality.
  • Currently these indexes will work on BTREE ONLY . However you can try if it works on other type of indexes .

Usage of Hypothetical indexes : 

Installation 

I am using PostgreSQL10 on CentOS7 . 

• 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 

Now Let’s make a table with 10 Crores rows as following : 

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

Now check COST of a query by running explain : 

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)

Now create the Hypothetical Index : 

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)

Now again do Explain to check if above index may be useful or not : 

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)

from both Explain command output we can clearly see the diffrence in cost and can also see that planner is using newly created hypothetical index .

We can Drop the index as follows : 

testdb=# select * from hypopg_drop_index(24797);
 hypopg_drop_index 
-------------------
 t
(1 row)

We can also check the estimated size of index created virtually as follows : 

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)

Now lets create actual index and see what will be the actual size

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)

As seen estimated and actual size is comparable .

I  Hope it clears the usage if hypothetical index in PostgreSQL .  In one of our blogs we learned about why index is not working  and also how to check on which tables index needed .  

In our future blogs we will talk about how you will get to know about exact index you need to make in database   . 

Stay Tuned to hello worlds . . . 

References : https://www.percona.com/blog/2019/06/21/hypothetical-indexes-in-postgresql/

Why Postgresql Index not working ? – Part 1

In last blog series we learned basics of Indexing. In this blog series we will understand on how to understand why query is not using the index. Basically sometimes what happens is that we assume when we run a query it should use a particular index but it actually either use diff. index or do not use index at all and do sequential scan. So in this blogs series we will work on understanding this case by case.

When we ask Postgres to execute a query it is executed in three steps:

• Parse – to validate the query sytax
• Planner – to plan how the query is executed like whether to do index scan or sequential scan or bitmap scan
• Executor – it will finally execute the planned tasks

Here we are more concerned about the Planner part, the entire purpose why the query needs planning is to reduce the total I/O (reading from disks/memory).

In first part we will work on example where Postgres use diff. index and primarily on the order by column and why it does that.

Lets understand it by example.

First create a table orders with indexes:

create table orders as select s as orderno, md5(random()::text) as orderitem , now() as order_created from generate_Series(1,1000000) s;

create index orderno_idx on orders(orderno);

create index order_created_idx on orders(order_created);

Now you see we have three columns orderno integer , orderitem text , order_created as date and two index one on orderno and other on order_created.

Now lets query :

we will do two query one which contains sorting on order_created and one without it , lets see which index which query use:

explain analyze select * from orders where orderno > 80000  order by order_created desc  limit 100 ;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..4.54 rows=100 width=45) (actual time=0.016..0.038 rows=100 loops=1)
   ->  Index Scan Backward using order_created_idx on orders  (cost=0.42..37829.43 rows=920121 width=45) (actual time=0.015..0.033 rows=100 loops=1)
         Filter: (orderno > 80000)
 Planning time: 0.170 ms
 Execution time: 0.051 ms
(5 rows)



explain analyze select * from orders where orderno < 80000    limit 100 ;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..4.22 rows=100 width=45) (actual time=3.121..3.137 rows=100 loops=1)
   ->  Index Scan using orderno_idx on orders  (cost=0.42..3028.31 rows=79879 width=45) (actual time=3.120..3.130 rows=100 loops=1)
         Index Cond: (orderno < 80000)
 Planning time: 0.052 ms
 Execution time: 3.150 ms
(5 rows)

Lets see the first query – we have put a where clause order > 80000 and order by order created .

• In this query normal assumption is it should use Btree index created on orderno as it will help on filtering the data fast
• But actually it is using the Btree index created on order created

Lets understand the reasons for doing so:

• Query with order clause – Very Important to understand this
  • As we have put the order by clause , in our query there will be around 9 million records matching for order > 80000 (as we have created 10 million record from 1 – 10 million)
  • And postgres thought rather than getting all 9 million record in memory and sorting its better we use the sorted index and check for values > 80000
  • there is a higher chance in this of getting values > 80000 in sorted order
• Query without order clause –
  • as we just needs to check for orderno > 80000 and it is already sorted in orderno index so order no index is used.

In this we understood that when we use order by clause the tendency of postgres is to use index which is there on order by field (as in this case order created).

In next blog we will figure out why postgres in doing bitmap scan rather than index scan.

Understanding Postgresql Indexes For Beginners- Part -6 (Postgresql Stats)

In last blog we learned about partial indexes. In this blog lets see how we can take advantage of Postgresql Stats for optimizing our queries and find out on which tables there are no indexes.

Postgresql provide various statistics about the system and provides us with lots of information on how we can optimize our databases.

In this blog we will discuss on sequential scans on tables and how we should optimize them.

Query to find table with sequential scan:

SELECT
    schemaname, relname,
    seq_scan, seq_tup_read,
    seq_tup_read / seq_scan as avg_seq_tup_read
FROM pg_stat_all_tables
WHERE seq_scan > 0
ORDER BY 5 DESC LIMIT 50;
 schemaname |       relname       | seq_scan | seq_tup_read | avg_seq_tup_read 
------------+---------------------+----------+--------------+------------------
 public     | tickets             |        3 |     24000000 |          8000000
 public     | student             |       18 |      5600010 |           311111
 pg_catalog | pg_class            |    22200 |      6789240 |              305
 pg_catalog | pg_index            |       17 |          503 |               29
 pg_catalog | pg_database         |    10553 |       168788 |               15
 public     | provider            |     1109 |         7763 |                7
 pg_catalog | pg_namespace        |       35 |          208 |                5
 pg_catalog | pg_attribute        |       22 |           96 |                4
 pg_catalog | pg_amproc           |        4 |            8 |                2
 pg_catalog | pg_am               |     6679 |         6679 |                1
 pg_catalog | pg_opclass          |        4 |            4 |                1
 pg_catalog | pg_authid           |        2 |            2 |                1
 pg_toast   | pg_toast_159046     |        1 |            0 |                0
 pg_toast   | pg_toast_159120     |        1 |            0 |                0
 pg_catalog | pg_publication      |     1109 |            0 |                0
 pg_catalog | pg_subscription_rel |        2 |            0 |                0

Now from the above view we can see that on tickets table 3 seq scan ran and row count in our table is also (8000000), which means there are bad queries or bad usage of the system is done as system is sequentially searching on this table.

Now what we should do here is figure out what all queries are which runs on the system via the postgresql and logs and see what index we can create so that this query dosen’t do sequential scan.

Understanding Postgresql Indexes For Beginners- Part -5 (Partial Indexes)

In last blog we learned about Gin indexes. By now we have covered the basic indexes we use in Postgresql.

Now lets look at one of the ways by which we can optimize our indexes for business specific use case.

Partial Indexing

Partial Indexing means that we can create index on some subset of the actual rows of the table.

This type of indexing is very very useful in cases where queries are required on the subset very often.

Lets take a example:

We have a ticketing table(subject text, status varchar) which contains subject of tickets and status whether the ticket is OPEN or CLOSED. Now lets say in our case we primarily search on open tickets only and in general CLOSED Tickets would be increasing constantly.

Lets assume on our ticket table size – 100 million rows , out of which 5 million are OPEN and 95 million are closed.

Now if we create a index on entire table (100 million rows) our index would also be large and every time if we want to search on OPEN ticket only , still we are loading that huge index.

Now with Partial index we can create Gin (text search) only on OPEN ticket rows our index would only work on OPEN Tickets (only 5 million rows), in such case our index would be very small and efficient and fast to load in memory.

How to use

lets create table for tickets only:

create table tickets(subject  tsvector , status varchar);


INSERT INTO tickets ( subject , status) SELECT md5(random()::text)::tsvector,CASE WHEN g < 500000 THEN 'OPEN' ELSE 'CLOSED' END FROM generate_series(1,8000000) as g;

Now lets create a complete index and partial index:

create index t_s_idx on tickets using gin(subject);

Partial Index :

create index t_s_open_idx on tickets using gin(subject) where status='OPEN';

create index t_s_open_idx on tickets using gin(subject) where <CONDITION EXPRESSION>

Size diff. between complete and partial

\di+ t_s_open_idx 
                            List of relations
 Schema |     Name     | Type  |  Owner   |  Table  | Size  | Description 
--------+--------------+-------+----------+---------+-------+-------------
 public | t_s_open_idx | index | postgres | tickets | 40 MB | 
(1 row)

saarthi=# \di+ t_s_idx 
                          List of relations
 Schema |  Name   | Type  |  Owner   |  Table  |  Size  | Description 
--------+---------+-------+----------+---------+--------+-------------
 public | t_s_idx | index | postgres | tickets | 662 MB | 
(1 row)

It is clearly visible that index size is 662 MB vs 40 MB and in general as OPEN tickets will not be constantly increasing this index would be of same size and mostly can remain in memory.

When Partial index would work

Partial Index would work only when the query also contain the search condition which is present while creating the index.

Like in our ticketing case index has condition status =’OPEN’ and if the query also has the condition status=’OPEN’ only then it works.

Most index like Btree, Gin , BRIN can be used as a partial index. Using partial index is one of the most powerful tool to speed up your system.

Understanding Postgresql Indexes For Beginners- Part -4 (Gin)

In the last blog we learned about BRIN index parameters and when to choose it. In this we will learn basic of Gin index.

Gin index is the that can speed up your full text search, lets understand how.

Gin Index

Gin Index – (Generalized Inverted Index) – An inverted index is an index data structure storing a mapping from content, such as words or numbers, to its locations in a document or a set of documents. In simple words, it is a hashmap like data structure that directs you from a word to a document or a web page. If you want more detail reading go to.

Now in case of Postgresql when we create any Gin index on a text column or the type of column is tsvector (A tsvector value is a sorted list of distinct lexemes, which are words that have been normalized to merge different variants of the same word )

Gin does is take all the distinct lexemes and break them into trigrams and map to the rows it points to and it will point to multiple rows if multiple rows contains that lexemes.

Now in this way when we search the any three character in the column and gin index will return the result in fast manner.

Let’s see this in action:

First create a table:

create table student(name  text , address tsvector);

Now let’s add some data:

INSERT INTO student ( name , address) SELECT md5(random()::text), md5(random()::text)::tsvector FROM generate_series(1,800000) as g;

Now lets check for search in name (text) and address (ts_vector fields)

 explain analyze select * from student where name ilike '%puneet%';
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..98897.03 rows=1887 width=64) (actual time=316.414..319.296 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on student  (cost=0.00..97708.33 rows=786 width=64) (actual time=307.882..307.883 rows=0 loops=3)
         Filter: (name ~~* '%puneet%'::text)
         Rows Removed by Filter: 266667
 Planning time: 0.089 ms
 Execution time: 319.353 ms



explain analyze select * from student where address @@ to_tsquery('gurgaon'); 
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..154940.76 rows=3956 width=74) (actual time=515.955..518.074 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on student  (cost=0.00..153545.16 rows=1648 width=74) (actual time=507.235..507.235 rows=0 loops=3)
         Filter: (address @@ to_tsquery('gurgaon'::text))
         Rows Removed by Filter: 266667
 Planning time: 0.125 ms
 Execution time: 518.102 ms
(8 rows)

Now you see its taking 391 ms and 518 ms

Let’s create indexes :

create index a_g_idx on student using gin(address);

CREATE EXTENSION IF NOT EXISTS pg_trgm;

create index name_g_idx on student using gin(name gin_trgm_ops);

Now let’s see the time:

explain analyze select * from student where address @@ to_tsquery('gurgaon'); 
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on student  (cost=55.25..13838.72 rows=4000 width=74) (actual time=0.067..0.068 rows=0 loops=1)
   Recheck Cond: (address @@ to_tsquery('gurgaon'::text))
   ->  Bitmap Index Scan on a_g_idx  (cost=0.00..54.25 rows=4000 width=0) (actual time=0.063..0.064 rows=0 loops=1)
         Index Cond: (address @@ to_tsquery('gurgaon'::text))
 Planning time: 0.472 ms
 Execution time: 0.140 ms


explain analyze select * from student where name ilike '%puneet%';
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on student  (cost=60.62..373.33 rows=80 width=74) (actual time=0.032..0.033 rows=0 loops=1)
   Recheck Cond: (name ~~* '%puneet%'::text)
   ->  Bitmap Index Scan on name_g_idx  (cost=0.00..60.60 rows=80 width=0) (actual time=0.029..0.030 rows=0 loops=1)
         Index Cond: (name ~~* '%puneet%'::text)
 Planning time: 6.547 ms
 Execution time: 0.083 ms

you see a huge difference in timings : from 319 ms to 0.14 and 518 to 0.083

Now we have seen the power of Gin Index in Postgresql and what text search in can provide.

Surely there are various parameters to configure and various other use cases of text search that can be done with gin.

Here our purpose was to understand its basics, in future we will work on details also.

In next blog we will work on Partial Indexs. Stay tuned and subscribe our blog for more such blogs.

Understanding Postgresql Indexes for Beginners – Part 4 (BRIN Indexes Optimization)

In last blog we learned about BRIN indexes , in this we will go in little detail on optimizing BRIN indexes and parameters for its optimization.

As we learned that BRIN index basically store the summary information (min and max ) for a particular page range(preferrably adjacent) such that it becomes easy to find which pages to cross check for a particular filter.

In this we will understand the BRIN index update mechanism and optimize the page range parameter.

When BRIN INDEX is updated

• Index Creation
  • When the index is created it will create summary for all the pages of the table.
• Table Update
  • For all page which are already summarized if any entry added / updated / removed , the index is updated in the transaction only
  • For new pages which are not summarized
    • Manual Update
      • By default BRIN does not auto update for this one needs to run the function –
        • select brin_summarize_new_values('<index name>'::regclass);
    • Auto Update
      • On autovacuum system will add the summary info for those blocks as well, we need to create index with auto vacuum on like this
        • create index c_t_brin_idx on calllog using brin(call_time) with (pages_per_range = 32,autosummarize = on);

How to optimize BRIN Indexes

Primary parameter to optimize BRIN indexes is the pages_per_range , this balance between no. of pages which needs to be scanned (because of lossy nature) vs no. of index entry created and index maintenance needs.

This parameter needs to be optimized based on individual needs , we will try to provide some guiding rules here, but before that we should do some math here and calculate the number of pages and rows per page:

Calculate no. of pages :

select relpages from pg_class where relname = 'student';;
 relpages 
----------
     5406

Now the number of pages is 5406

Now lets calculate total records :

select count(*) from student;
  count  
---------
 1000000

Now we have total rows as 1 million , lets count no. of record per page:

select 1000000/5406 as row_per_page;
 row_per_page 
--------------
          184

Now we see that row per page is 184 in our case, this will be diff. in you case so before proceeding check in your table.

and default value is 128 in page per range — so minimum values our index need to filter for every query is : 184*128 = 23552

Now lets try to see the rules, also rules needs to be balanced for best performance as in databases everything depends on your needs:

• Table should be Large – something > 5 millions
  • if less data then benefit of small size of index would be less and Btree would also be small.
• Data should primarily be INSERT Only
  • If we are constantly updating the data and also the data is updated in such a way that single value occur in multiple page range (eg: page 1-10 raneg is 100 -550 and page 11-20 range is 250-800), in this case the system needs to query both range pages which will slow down the filter as pages increase
• Data should be inserted in IN ORDER
  • If we are inserting data in such a way that the column in which we are creating index that single value occur in multiple page range (eg: page 1-10 raneg is 100 -550 and page 11-20 range is 250-800), in this case the system needs to query both range pages which will slow down the filter as pages increase
• Search record are generally in large numbers > 1000
  • if we are selecting single value Btree will generally out perform the BRIN , but if we are selecting the no. of records in some 1000’s , BRIN will start to perform better at large count.

Al in all one needs to be very clear in the above points before deciding to use BRIN.

In next blog we will move to Gin Indexes.

Subscribe our blog for more indepth tutorials.

Understanding Postgresql Indexes for Beginners – Part 3 (BRIN Index)

In last blogs we understood basics of Btree and Multi Column Btree. In this blog we will see Postgresql BRIN Indexes.

BRIN Index

Block Range Index – It is a revolutionary idea first proposed in 2015 by Alvaro.

The most fundamental difference in this index is rather than storing the actual values in the index and point to rows. It actually stores the range information about the pages in which the rows are stored.

For eg: Lets say we have a student table with 1000 roll no. and these rows are stored in 100 pages and pages 1-10 are adjacent then 10-20 and then 20-30 and so on. If we use BTree index it would create a tree of 1000 roll no values and no. of nodes say for eg in this tree would be 1000. But what BRIN would do is it will store the max value of roll no and min values of roll no for page ranges (lets say page range is of 10 pages) so BRIN index would store 1 values(min and max) for page range 1-20 then 1 values for 10-20 and so on effectively it would store only 10 values rather than 1000.

You see this huge diff. between the values stored in BRIN vs BTree. This marks for huge performance improvement for storing of indexes but i think you would have realized that this is a lossy index

Lossy What?

What we meant by lossy here, lets go by the example again as index is only storing roll no min and roll no max value in pages between 1-10 (which contains 100 roll no.) , now if you check for lest say roll no. – 10 and mix and max value for page 1-10 is 1 – 100 . Now as it is just min and max we are still not sure whether the values 10 exist in the pages or not. For this system needs to go to every row in pages 1-10 and check whether roll no exist or not. This is what we meant as lossy , means index is not confirmng whether the value exist or not.

When to use BRIN

If your data is such that which mostly insert only like logs or history kind of stuff and your business requirement is to query recent logs or some date range logs then it is great to use BRIN as index as this would drastically reduce size of index, index maintenance , you would generally be searching for a range.

Lets take a real life example, create a table with 100000 logs data:

CREATE TABLE calllog (call_time timestamp not null, call_result text , no_of_participant integer); 

INSERT INTO calllog ( call_time, no_of_participant, call_result) SELECT g, CURRENT_TIMESTAMP + ( g || 'minute' ) :: interval, random() * 10, md5(g::text) FROM generate_series(1,8000000) as g;

Now lets search for some cal logs between now() and now() – 5 hour:

explain analyze  select * from calllog  where call_time  between now()  and  now() - interval '5 hour';
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Seq Scan on calllog  (cost=0.00..254764.68 rows=1 width=45) (actual time=1469.561..1469.561 rows=0 loops=1)
   Filter: ((call_time >= now()) AND (call_time <= (now() - '05:00:00'::interval)))
   Rows Removed by Filter: 8000000
 Planning time: 0.280 ms
 Execution time: 1469.596 ms

Lets see what happens is we use BTree here :

create index ct_idx on calllog(call_time);


explain analyze  select * from calllog  where call_time  between now()  and  now() - interval '5 hour';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Index Scan using ct_idx on calllog  (cost=0.44..8.46 rows=1 width=45) (actual time=0.040..0.041 rows=0 loops=1)
   Index Cond: ((call_time >= now()) AND (call_time <= (now() - '05:00:00'::interval)))
 Planning time: 2.214 ms
 Execution time: 0.086 ms

You see a huge diff. from 1469 ms to 0.086 ms.

Now lets see what brin would do:

create index c_t_brin_idx  on calllog using brin(call_time) with (pages_per_range = 32,autosummarize = on);

drop  index ct_idx ;

 explain analyze  select * from calllog  where call_time  between now()  and  now() - interval '5 hour';
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on calllog  (cost=45.02..11385.57 rows=1 width=45) (actual time=2.259..2.260 rows=0 loops=1)
   Recheck Cond: ((call_time >= now()) AND (call_time <= (now() - '05:00:00'::interval)))
   ->  Bitmap Index Scan on c_t_brin_idx  (cost=0.00..45.02 rows=3423 width=0) (actual time=2.256..2.256 rows=0 loops=1)
         Index Cond: ((call_time >= now()) AND (call_time <= (now() - '05:00:00'::interval)))
 Planning time: 0.202 ms
 Execution time: 2.315 ms

You see 2.315 ms still very less than 1469 ms but greater than 0.086 ms.

Why BRIN over BTree when Btree is speedy??

1. Size of BRIN vs BTree

\di+ c_t_brin_idx 
                             List of relations
 Schema |     Name     | Type  |  Owner   |  Table  |  Size  | Description 
--------+--------------+-------+----------+---------+--------+-------------
 public | c_t_brin_idx | index | postgres | calllog | 120 kB | 


 \di+ ct_idx 
                          List of relations
 Schema |  Name  | Type  |  Owner   |  Table  |  Size  | Description 
--------+--------+-------+----------+---------+--------+-------------
 public | ct_idx | index | postgres | calllog | 171 MB | 

You see there is huge difference in size and when there are lot of tables and indexes in your system such that you cannot have this 172MB in memory, those cases BRIN becomes very powerful

2. Index Maintenance – BTree indexes are costly to maintain as they would changes in every DML operation and has to be done in same transaction. While BRIN is put this offloading to vaccuming.

By Now i guess you understood lots of power of BRIN indexes and also the benefits over BTree.

You can ask your questions in the comment section we will try best to answer in your specific cases:

Next we will see parameters to optimize BRIN and then move to Gin Indexes.

Please subscribe for such indepth blogs.