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