On Mon, 24 Jun 2024 at 04:38, <masahiro.ik...@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