on Thu, 14 Sept 2023 at 23:43, David Rowley <dgrowle...@gmail.com> wrote: > This likely is due to the query planner not giving any preference to the > index that allows more quals to go into the index condition. Once the > selectivity estimate gets as low as 1 row then the costs between each index > don't vary very much. It's possible the PK index didn't come out any cheaper, > or that add_path() saw the costs as (fuzzily) the same.
I understand that this is just a cost estimate, not a check to see if the SQL condition matches the index. Thank you for telling me. > Does v14 run faster if you force the tbl_pkey to be used? (perhaps you could > do that just by dropping the other index if you're using a test instance > that's not needed by anyone else). The following example shows a table with 1 million tuples: * The cost of using PK was higher than the cost of using user index. * It was faster to use PK. Index Scan using tbl_ix1 on tbl (cost=0.43..0.67 rows=1 width=61) (actual time=0.016..185.013 rows=1 loops=1) Index Cond: (((a)::text = '1000000000'::text) AND ((b)::text = '1000000000'::text) AND ((c)::text = '1000000000'::text) AND ((d)::text = '1000000000'::text) AND (h = 1)) Filter: ((e)::text = '1000000000'::text) Rows Removed by Filter: 1000000 Planning Time: 0.407 ms Execution Time: 185.031 ms Index Only Scan using tbl_pkey on tbl (cost=0.56..0.79 rows=1 width=61) (actual time=0.026..0.028 rows=1 loops=1) Index Cond: ((a = '1000000000'::text) AND (b = '1000000000'::text) AND (c = '1000000000'::text) AND (d = '1000000000'::text) AND (e = '1000000000'::text) AND (h = 1)) Heap Fetches: 1 Planning Time: 0.355 ms Execution Time: 0.043 ms I should probably configure the statistics to account for changes in planner behavior. Therefore, I will consider appropriate measures. Regards, Ryo
