RE: Improve EXPLAIN output for multicolumn B-Tree Index - Mailing list pgsql-hackers

> > I think the better choice would be adding an IndexAmRoutine->amexplain
> > support function, which would get called in e.g. explain.c's
> > ExplainIndexScanDetails to populate a new "Index Scan Details" (name
> > to be bikeshed) subsection of explain plans. This would certainly be
> > possible, as the essentials for outputting things to EXPLAIN are
> > readily available in the explain.h header.
>
> Yes, that's one of my concerns. I agree to add IndexAmRoutine->amexplain is better
> because we can support several use cases.
>
> Although I'm not confident to add only IndexAmRoutine->amexplain is enough now, I'll
> make a PoC patch to confirm it.

I attached the patch adding an IndexAmRoutine->amexplain.

This patch changes following.
* add a new index AM function "amexplain_function()" and it's called in ExplainNode()
   Although I tried to add it in ExplainIndexScanDetails(), I think it's not the proper place to
   show quals. So, amexplain_function() will call after calling show_scanqual() in the patch.
* add "amexplain_function" for B-Tree index and show "Non Key Filter" if VERBOSE is specified
   To avoid confusion with INCLUDE-d columns and non-index column "Filter", I've decided to
   output only with the VERBOSE option. However, I'm not sure if this is the appropriate solution.
   It might be a good idea to include words like 'b-tree' to make it clear that it's an output specific
   to b-tree index.

-- Example dataset
CREATE TABLE test (id1 int, id2 int, id3 int, value varchar(32));
CREATE INDEX test_idx ON test(id1, id2, id3);  -- multicolumn B-Tree index
INSERT INTO test (SELECT i % 2, i, i, 'hello' FROM generate_series(1,1000000) s(i));
ANALYZE;

-- The output is same as without this patch if it can search efficiently
=# EXPLAIN (VERBOSE, ANALYZE, BUFFERS, MEMORY, SERIALIZE) SELECT id3 FROM test WHERE id1 = 1 AND id2 = 101;
                                                        QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_idx on public.test  (cost=0.42..4.44 rows=1 width=4) (actual time=0.058..0.060 rows=1
loops=1)
   Output: id3
   Index Cond: ((test.id1 = 1) AND (test.id2 = 101))
   Heap Fetches: 0
   Buffers: shared hit=4
 Planning:
   Memory: used=14kB  allocated=16kB
 Planning Time: 0.166 ms
 Serialization: time=0.009 ms  output=1kB  format=text
 Execution Time: 0.095 ms
(10 rows)

-- "Non Key Filter" will be displayed if it will scan index tuples and filter them
=# EXPLAIN (VERBOSE, ANALYZE, BUFFERS, MEMORY, SERIALIZE) SELECT id3 FROM test WHERE id1 = 1 AND id3 = 101;
                                                           QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using test_idx on public.test  (cost=0.42..12724.10 rows=1 width=4) (actual time=0.055..69.446 rows=1
loops=1)
   Output: id3
   Index Cond: ((test.id1 = 1) AND (test.id3 = 101))
   Heap Fetches: 0
   Non Key Filter: (test.id3 = 101)
   Buffers: shared hit=1920
 Planning:
   Memory: used=14kB  allocated=16kB
 Planning Time: 0.113 ms
 Serialization: time=0.004 ms  output=1kB  format=text
 Execution Time: 69.491 ms
(11 rows)

Although I plan to support "Rows Removed by Non Key Filtered"(or "Skip Scan Filtered"),
I'd like to know whether the current direction is good. One of my concerns is there might
be a better way to exact quals for boundary conditions in btexplain().

Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION