January 19th, 2018

trekking, Himalaya

Debugging indexes in PostgreSQL with plantuner

Now it's more convenient to define plantuner.only_index variable to specify indexes visible to optimizer.
\d bookmarks
            Table "public.bookmarks"
 Column | Type  | Collation | Nullable | Default
 jb     | jsonb |           |          |
    "bookmarks_jb_idx" gin (jb)
    "bookmarks_jb_idx1" gin (jb jsonb_path_ops)
    "bookmarks_jb_idx2" gin (jb jsonb_path_value_ops)
    "bookmarks_jb_idx3" gin (jb jsonb_laxpath_value_ops)
    "bookmarks_jb_idx4" gin (jb jsonb_value_path_ops)

[local]:6666 postgres@postgres=# LOAD 'plantuner';
Time: 0.372 ms
[local]:6666 postgres@postgres=# set plantuner.only_index = 'bookmarks_jb_idx3';
EXPLAIN (analyze, costs off)
SELECT COUNT(*) FROM bookmarks
WHERE jb @? '$.tags[*] ? (@.term == "NYC")';
                                           QUERY PLAN
 Aggregate (actual time=0.572..0.572 rows=1 loops=1)
   ->  Bitmap Heap Scan on bookmarks (actual time=0.101..0.543 rows=285 loops=1)
         Recheck Cond: (jb @? '$."tags"[*]?(@."term" == "NYC")'::jsonpath)
         Heap Blocks: exact=285
         ->  Bitmap Index Scan on bookmarks_jb_idx3 (actual time=0.064..0.064 rows=285 loops=1)
               Index Cond: (jb @? '$."tags"[*]?(@."term" == "NYC")'::jsonpath)
 Planning Time: 1.702 ms
 Execution Time: 0.612 ms
(8 rows)

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.

Collapse )