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.
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.
Finally, does this change the index AM API? Does this add another
scankey argument to ->amrescan?

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

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


Kind regards,

Matthias van de Meent