RE: Access plan selection logic PG9.2 -> PG14

on Thu, 14 Sept 2023 at 23:43, David Rowley <dgrowleyml(at)gmail(dot)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