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

On Fri, 15 Sept 2023 at 21:13, Ryo Yamaji (Fujitsu)
<yamaji(dot)ryo(at)fujitsu(dot)com> wrote:
> 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.

If I were you, I'd try what Adrian mentioned and run ANALYZE on this table.

I tried the following and I can only get it to use the tbl_ix1 index
if I don't run analyze. After running analyze, PostgreSQL 14.7 seems
it would rather Seq scan than use the tbl_ix1 index after dropping the
primary key constriant.

drop table if exists tbl;
create table tbl (a text, b text, c text, d text, e text, h int);
insert into tbl select
'1000000000','1000000000','1000000000','1000000000',x::text,1 from
generate_Series(999000001,1000000000)x;
create index tbl_ix1 on tbl(a,b,c,d,h);
alter table tbl add constraint tbl_pkey primary key (a,b,c,d,e,h);

explain analyze select a,b,c,d,h from tbl where a = '1000000000' and b
= '1000000000' and c = '1000000000' and d = '1000000000' and e =
'1000000000' and h = 1;

Index Scan using tbl_ix1 on tbl (cost=0.42..8.46 rows=1 width=132)
(actual time=121.062..121.062 rows=1 loops=1)
Index Cond: ((a = '1000000000'::text) AND (b = '1000000000'::text)
AND (c = '1000000000'::text) AND (d = '1000000000'::text) AND (h = 1))
Filter: (e = '1000000000'::text)
Rows Removed by Filter: 999999
Planning Time: 0.266 ms
Execution Time: 121.077 ms
(6 rows)

analyze tbl;

explain analyze select a,b,c,d,h from tbl where a = '1000000000' and b
= '1000000000' and c = '1000000000' and d = '1000000000' and e =
'1000000000' and h = 1;

Index Only Scan using tbl_pkey on tbl (cost=0.55..4.58 rows=1
width=48) (actual time=0.071..0.072 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: 0
Planning Time: 0.146 ms
Execution Time: 0.087 ms
(5 rows)

David