> I am unable to decide whether reporting the bound quals is just enough to decide the efficiency of index without
knowingthe difference in the number of index tuples selectivity and heap tuple selectivity. The difference seems to be
abetter indicator of index efficiency whereas the bound quals will help debug the in-efficiency, if any.
> Also, do we want to report bound quals even if they are the same as index conditions or just when they are
different?
Thank you for your comment. After receiving your comment, I thought it would be better to also report information that
wouldmake the difference in selectivity understandable. One idea I had is to output the number of index tuples
inefficientlyextracted, like “Rows Removed by Filter”. Users can check the selectivity and efficiency by looking at the
number.
Also, I thought it would be better to change the way bound quals are reported to align with the "Filter". I think it
wouldbe better to modify it so that it does not output when the bound quals are the same as the index conditions.
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
Rows Removed by Index Filter: 499999 -- New. Output when ANALYZE option is specified
Planning Time: 0.354 ms
Execution Time: 279.908 ms
(7 rows)
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
