From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | "Ryo Yamaji (Fujitsu)" <yamaji(dot)ryo(at)fujitsu(dot)com> |
Cc: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Access plan selection logic PG9.2 -> PG14 |
Date: | 2023-09-15 09:50:08 |
Message-ID: | CAApHDvoyiAzH5s5A6f51nK_DpjET2jBT0ZUC1KGWz524hkw9hw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Mueller | 2023-09-15 16:55:59 | update from 13 to16 |
Previous Message | Ryo Yamaji (Fujitsu) | 2023-09-15 09:29:45 | RE: Access plan selection logic PG9.2 -> PG14 |