...

или внеочередные заметки


Previous Entry Share Next Entry
Using plantuner for debugging indexes in PostgreSQL
trekking, Himalaya
obartunov
Many years ago we developed Plantuner to facilitate the process of debugging indexes - we wanted to specify which index out of bunch of them to use. The trick is to hide all indexes and uncover for optimizer the specific one.


Let's consider, for example, the case of testing opclasses (contrib/intarray) with parameters (our recent improvement of indexing infrastructure), specifically, the size of signature.

\d test_int
               Table "public.test_int"
 Column |   Type    | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
 id     | integer   |           |          |
 i      | integer[] |           |          |
Indexes:
    "test_int_i_idx" gist (i gist__intbig_ops (siglen='20'))
    "test_int_i_idx1" gist (i gist__intbig_ops (siglen='40'))
    "test_int_i_idx10" gist (i gist__intbig_ops)
    "test_int_i_idx2" gist (i gist__intbig_ops (siglen='60'))
    "test_int_i_idx3" gist (i gist__intbig_ops (siglen='80'))
    "test_int_i_idx4" gist (i gist__intbig_ops (siglen='100'))
    "test_int_i_idx5" gist (i gist__intbig_ops (siglen='120'))
    "test_int_i_idx6" gist (i gist__intbig_ops (siglen='140'))
    "test_int_i_idx7" gist (i gist__intbig_ops (siglen='160'))
    "test_int_i_idx8" gist (i gist__intbig_ops (siglen='180'))
    "test_int_i_idx9" gist (i gist__intbig_ops (siglen='200'))


The query I'm testing is
=# select count(*) from test_int where i && '{1,2}'::int[];

First I need to load 'plantuner' and 'hide' all indexes using plantuner's variable plantuner.forbid_index.
=# LOAD 'plantuner';
=# set plantuner.forbid_index = 'test_int_i_idx, test_int_i_idx1,test_int_i_idx2,test_int_i_idx3,test_int_i_idx4,test_int_i_idx5,test_int_i_idx6,test_int_i_idx7,test_int_i_idx8,test_int_i_idx9,test_int_i_idx10';
After that I can specify which index to use
=# set plantuner.enable_index = test_int_i_idx;
=# explain (analyze, costs off)  select count(*) from test_int where i && '{1,2}'::int[];
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Aggregate (actual time=489.932..489.932 rows=1 loops=1)
   ->  Bitmap Heap Scan on test_int (actual time=489.929..489.929 rows=0 loops=1)
         Recheck Cond: (i && '{1,2}'::integer[])
         Rows Removed by Index Recheck: 64460
         Heap Blocks: exact=5883
         ->  Bitmap Index Scan on test_int_i_idx (actual time=10.784..10.784 rows=64460 loops=1)
               Index Cond: (i && '{1,2}'::integer[])
 Planning time: 0.102 ms
 Execution time: 489.973 ms
(9 rows)
We see that index built using short signature (siglen=20) isn't effective - too many rechecks, so let's try longer signature (siglen=60):
=# set plantuner.enable_index = test_int_i_idx2;
=# explain (analyze, costs off)  select count(*) from test_int where i && '{1,2}'::int[];
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate (actual time=0.010..0.010 rows=1 loops=1)
   ->  Bitmap Heap Scan on test_int (actual time=0.008..0.008 rows=0 loops=1)
         Recheck Cond: (i && '{1,2}'::integer[])
         ->  Bitmap Index Scan on test_int_i_idx2 (actual time=0.006..0.006 rows=0 loops=1)
               Index Cond: (i && '{1,2}'::integer[])
 Planning time: 0.104 ms
 Execution time: 0.037 ms
(7 rows)
This index is 13216 times faster !

If we compare index test_int_i_idx10, built with default signature length (252 bytes), then we see that performance not increased, but even a bit slower.

explain (analyze, costs off)  select count(*) from test_int where i && '{1,2}'::int[];
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Aggregate (actual time=0.009..0.009 rows=1 loops=1)
   ->  Bitmap Heap Scan on test_int (actual time=0.007..0.007 rows=0 loops=1)
         Recheck Cond: (i && '{1,2}'::integer[])
         ->  Bitmap Index Scan on test_int_i_idx10 (actual time=0.006..0.006 rows=0 loops=1)
               Index Cond: (i && '{1,2}'::integer[])
 Planning time: 0.146 ms
 Execution time: 0.051 ms
(7 rows)
That's explained by the sizes of indexes, 13MB vs 37MB:
=# \di+ test*
                                List of relations
 Schema |       Name       | Type  |  Owner   |  Table   |  Size   | Description
--------+------------------+-------+----------+----------+---------+-------------
 public | test_int_i_idx   | index | postgres | test_int | 7200 kB |
 public | test_int_i_idx1  | index | postgres | test_int | 9760 kB |
 public | test_int_i_idx10 | index | postgres | test_int | 37 MB   |
 public | test_int_i_idx2  | index | postgres | test_int | 13 MB   |
 public | test_int_i_idx3  | index | postgres | test_int | 15 MB   |
 public | test_int_i_idx4  | index | postgres | test_int | 18 MB   |
 public | test_int_i_idx5  | index | postgres | test_int | 19 MB   |
 public | test_int_i_idx6  | index | postgres | test_int | 23 MB   |
 public | test_int_i_idx7  | index | postgres | test_int | 24 MB   |
 public | test_int_i_idx8  | index | postgres | test_int | 28 MB   |
 public | test_int_i_idx9  | index | postgres | test_int | 29 MB   |
(11 rows)
I use function hide_all_ind(relation) to disable all indexes (set plantuner.forbid_index) of the 'relation', so it is convenient to set plantuner.enable_index. Just do

SELECT hide_all_ind('test_int');


-- load 'plantuner' first CREATE OR REPLACE FUNCTION hide_all_ind(TEXT) RETURNS text AS $$ DECLARE indexes text; taboid oid; temp text; tab ALIAS FOR $1; BEGIN temp := '^(' || tab || ')$'; SELECT c.oid INTO taboid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ temp AND pg_catalog.pg_table_is_visible(c.oid); SELECT array_to_string ( ARRAY ( SELECT c2.relname FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND conindid = i.indexrelid AND contype IN ('p','u','x')) WHERE c.oid = taboid AND c.oid = i.indrelid AND i.indexrelid = c2.oid ORDER BY c2.relname ),',') INTO indexes; IF indexes <> '' THEN UPDATE pg_settings SET setting = indexes WHERE name = 'plantuner.forbid_index'; END IF; RETURN indexes; END; $$ LANGUAGE plpgsql;

  • 1
nice work! this is a great extension to postgresql, and great to see that you're still using it today! i'm curious, are you using this extension against 9.6 and 10 databases? also, how does plantuner compare with the pg_hint_plan extension?

Yes, we use it with 9.6+ versions. Plantuner is not about hinting, it doesn't modify a query.

  • 1
?

Log in

No account? Create an account