> > 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
v2-0001-Support-Non-Key-Filter-for-multicolumn-B-Tree-Ind.patch
Description: v2-0001-Support-Non-Key-Filter-for-multicolumn-B-Tree-Ind.patch
