RE: Improve EXPLAIN output for multicolumn B-Tree Index

> On Mon, 24 Jun 2024 at 04:38, <Masahiro.Ikeda@nttdata.com> wrote:
> >
> > In my local PoC patch, I have modified the output as follows, what do you think?
> >
> > =# EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test WHERE id1 = 1 AND id2 =
> 101;
> >                                                        QUERY PLAN
> > ----------------------------------------------------------------------
> > ---------------------------------------------------
> >  Index Scan using test_idx on ikedamsh.test  (cost=0.42..8.45 rows=1 width=18)
> (actual time=0.082..0.086 rows=1 loops=1)
> >    Output: id1, id2, id3, value
> >    Index Cond: ((test.id1 = 1) AND (test.id2 = 101))  -- If it’s efficient, the output
> won’t change.
> >  Planning Time: 5.088 ms
> >  Execution Time: 0.162 ms
> > (5 rows)
> >
> > =# EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test WHERE id1 = 1 AND id3 =
> 101;
> >                                                           QUERY PLAN
> > ----------------------------------------------------------------------
> > ---------------------------------------------------------
> >  Index Scan using test_idx on ikedamsh.test  (cost=0.42..12630.10 rows=1
> width=18) (actual time=0.175..279.819 rows=1 loops=1)
> >    Output: id1, id2, id3, value
> >    Index Cond: (test.id1 = 1)                 -- Change the output. Show only the
> bound quals.
> >    Index Filter: (test.id3 = 101)              -- New. Output quals which are not
> used as the bound quals
> 
> I think this is too easy to confuse with the pre-existing 'Filter'
> condition, which you'll find on indexes with INCLUDE-d columns or filters on non-index
> columns.

Thanks for your comment. I forgot the case.

> Furthermore, I think this is probably not helpful (maybe even harmful) for index types
> like GIN and BRIN, where index searchkey order is mostly irrelevant to the index shape
> and performance.

Yes, I expected that only B-Tree index support the feature.

> Finally, does this change the index AM API? Does this add another scankey argument to
> ->amrescan?

Yes, I think so. But since I'd like to make users know the index scan will happen without
ANALYZE, I planned to change amcostestimate for "Index Filter" and amrescan() for 
"Rows Removed by Index Filter".

> >    Rows Removed by Index Filter: 499999    -- New. Output when ANALYZE option
> is specified
> 
> Separate from the changes to Index Cond/Index Filter output changes I think this can
> be useful output, though I'd probably let the AM specify what kind of filter data to
> display.
> E.g. BRIN may well want to display how many ranges matched the predicate, vs how
> many ranges were unsummarized and thus returned; two conditions which aren't as
> easy to differentiate but can be important debugging query performance.

OK, thanks. I understood that it would be nice if we could customize to output information
specific to other indexes like BRIN.

> >  Planning Time: 0.354 ms
> >  Execution Time: 279.908 ms
> > (7 rows)
> 
> Was this a test against the same dataset as the one you'd posted your measurements of
> your first patchset with? The execution time seems to have slown down quite
> significantly, so if the testset is the same then this doesn't bode well for your patchset.

Yes, the reason is that the cache hit ratio is very low since I tested after I restarted the 
machine. I had to add BUFFERS option.

Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION