From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, william(dot)duclot(at)gmail(dot)com, PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower |
Date: | 2023-10-08 08:26:43 |
Message-ID: | CAMbWs4-eNVNTNc94eF+O_UwHYKv43vyMurhcdqMV=Ht5fehcOg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
On Thu, Sep 28, 2023 at 11:51 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Thu, 28 Sept 2023 at 16:22, Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> > It seems that optimizing IS NULL quals is more complex than optimizing
> > IS NOT NULL quals. I also wonder if it's worth the trouble to optimize
> > IS NULL quals.
>
> I'm happy to reduce the scope of this patch. As for what to cut, I
> think if we're doing a subset then we should try to do that subset in
> a way that best leaves things open for phase 2 at some later date.
I had a go at supporting IS NULL quals and ended up with the attached.
The patch generates a new constant-FALSE RestrictInfo that is marked
with the same required_relids etc as the original one if it is an IS
NULL qual that can be reduced to FALSE. Note that the original
rinfo_serial is also copied to the new RestrictInfo.
One thing that is not great is that we may have 'FALSE and otherquals'
in the final plan, as shown by the plan below which is from the new
added test case.
+explain (costs off)
+select * from pred_tab t1 left join pred_tab t2 on true left join pred_tab
t3 on t2.a is null and t2.b = 1;
+ QUERY PLAN
+---------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: (false AND (t2.b = 1))
+ -> Seq Scan on pred_tab t2
+ -> Result
+ One-Time Filter: false
+(8 rows)
Maybe we can artificially reduce it to 'FALSE', but I'm not sure if it's
worth the trouble.
Thanks
Richard
Attachment | Content-Type | Size |
---|---|---|
v5-0001-Reduce-NullTest-quals-to-constant-TRUE-or-FALSE.patch | application/octet-stream | 29.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Hans Buschmann | 2023-10-08 13:16:52 | AW: BUG #18147: ERROR: invalid perminfoindex 0 in RTE with relid xxxxx |
Previous Message | Patrick Peralta | 2023-10-07 18:22:58 | Re: BUG #18149: Incorrect lexeme for english token "proxy" |
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Guo | 2023-10-08 10:52:38 | Re: pg16: XX000: could not find pathkey item to sort |
Previous Message | Michał Kłeczek | 2023-10-08 05:27:06 | Re: Draft LIMIT pushdown to Append and MergeAppend patch |