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