pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedregression in PG 15.1
8+ messages / 3 participants
[nested] [flat]
* regression in PG 15.1
@ 2022-11-28 14:41 Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 8+ messages in thread
From: Scott Ribe @ 2022-11-28 14:41 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Table is partitioned on a column tbl_id, with an increasing "transaction id".
select * from tbl where txid > something and tbl_id = someval;
^^^ works as normal, searches the single partition, taking 0.044ms
select * from tbl where txid > something and tbl_id = (select id from reftbl where name = 'someval');
^^^ fails to exclude partitions, kicks off parallel scans on all 142, takes 23,170ms
Queries in question are NOT auto-generated from some ORM, so there is an obvious easy workaround. Before trying that, and before digging into full table defs here, does anyone have an idea how to nudge the planner toward excluding the partitions.
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: regression in PG 15.1
@ 2022-11-28 14:44 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 8+ messages in thread
From: Scott Ribe @ 2022-11-28 14:44 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
I should have noted that vacuum analyze on parent table did not help.
> On Nov 28, 2022, at 7:41 AM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
>
> Table is partitioned on a column tbl_id, with an increasing "transaction id".
>
> select * from tbl where txid > something and tbl_id = someval;
>
> ^^^ works as normal, searches the single partition, taking 0.044ms
>
> select * from tbl where txid > something and tbl_id = (select id from reftbl where name = 'someval');
>
> ^^^ fails to exclude partitions, kicks off parallel scans on all 142, takes 23,170ms
>
> Queries in question are NOT auto-generated from some ORM, so there is an obvious easy workaround. Before trying that, and before digging into full table defs here, does anyone have an idea how to nudge the planner toward excluding the partitions.
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> https://www.linkedin.com/in/scottribe/
>
>
>
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: regression in PG 15.1
@ 2022-11-28 14:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 2 replies; 8+ messages in thread
From: Tom Lane @ 2022-11-28 14:44 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Scott Ribe <scott_ribe@elevated-dev.com> writes:
> select * from tbl where txid > something and tbl_id = (select id from reftbl where name = 'someval');
> ^^^ fails to exclude partitions, kicks off parallel scans on all 142, takes 23,170ms
Why do you say this is a regression? From what I know of the partition
pruning logic (admittedly not a whole lot), I don't think we'd have
ever pruned on the basis of such a constraint.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: regression in PG 15.1
@ 2022-11-28 14:50 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2022-11-28 14:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Nov 28, 2022 at 7:45 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Scott Ribe <scott_ribe@elevated-dev.com> writes:
> > select * from tbl where txid > something and tbl_id = (select id from
> reftbl where name = 'someval');
> > ^^^ fails to exclude partitions, kicks off parallel scans on all 142,
> takes 23,170ms
>
> Why do you say this is a regression? From what I know of the partition
> pruning logic (admittedly not a whole lot), I don't think we'd have
> ever pruned on the basis of such a constraint.
>
>
The second bullet point here indicates the subquery should prune during
execution.
https://www.postgresql.org/docs/15/ddl-partitioning.html#DDL-PARTITION-PRUNING
During actual execution of the query plan. Partition pruning may also be
performed here to remove partitions using values which are only known
during actual query execution. This includes values from subqueries [...]
That subquery seems like it should meet whatever criteria there is for this
runtime pruning. Namely, it must return zero or one rows and it not
correlated with the rest of the query.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: regression in PG 15.1
@ 2022-11-28 14:57 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2022-11-28 14:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Nov 28, 2022, at 7:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Why do you say this is a regression? From what I know of the partition
> pruning logic (admittedly not a whole lot), I don't think we'd have
> ever pruned on the basis of such a constraint.
Because performance of the query tanked. If it's not a change in table pruning, then it's a change in whether/how/which indexes are used. I guess I'll have to dig into it more and get back to the list--probably tomorrow.
Meantime, thanks for the clue to help guide me here.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: regression in PG 15.1
@ 2022-11-28 15:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2022-11-28 15:15 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Scott Ribe <scott_ribe@elevated-dev.com> writes:
>> On Nov 28, 2022, at 7:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Why do you say this is a regression? From what I know of the partition
>> pruning logic (admittedly not a whole lot), I don't think we'd have
>> ever pruned on the basis of such a constraint.
> Because performance of the query tanked.
Compared to what? If you said something like "it worked as I expected
in Postgres x.y", that would be something to go on.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: regression in PG 15.1
@ 2022-11-28 15:18 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2022-11-28 15:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Nov 28, 2022, at 8:15 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Compared to what? If you said something like "it worked as I expected
> in Postgres x.y", that would be something to go on.
14.4--sorry, *I* know I stay pretty up to date and am never skipping over major versions, LOL
I will reload data into 14.4 and explain analyze and provide better data.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: regression in PG 15.1
@ 2022-11-29 04:55 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 8+ messages in thread
From: Scott Ribe @ 2022-11-29 04:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
OK, this is not a regression, but a combination of things:
- in 14, it is searching all partitions, but is fast enough that I never noticed (log_min_duration is 1s)
- in 15, at first try it took 30s for JIT, oops
- with that turned off, performance was still really bad
- statistics were not properly updated after the upgrade to 15
- and a big jump was made in a monotonically increasing serial being searched on
- instead of 142 index scans, 141 returning 0, and 1 returning a few rows, we got 142 seq scans
- problem fixed itself while I was still figuring it all out, because of normal vacuum thresholds
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2022-11-29 04:55 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-28 14:41 regression in PG 15.1 Scott Ribe <scott_ribe@elevated-dev.com>
2022-11-28 14:44 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-11-28 14:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-28 14:50 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-28 14:57 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-11-28 15:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-28 15:18 ` Scott Ribe <scott_ribe@elevated-dev.com>
2022-11-29 04:55 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox