RE: Improve EXPLAIN output for multicolumn B-Tree Index

> > 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