Обсуждение: regression in PG 15.1
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, andbefore 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/
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, andbefore 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/ > > > > >
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
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.
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.
> 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 indexesare 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.
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
> 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.
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