Re: BUG #16251: ::text type casting of a constant breaks query performance - Mailing list pgsql-bugs

From Jeff Janes
Subject Re: BUG #16251: ::text type casting of a constant breaks query performance
Date
Msg-id CAMkU=1zcy8fX_XEQVvETD779GnMc=g_yBpftdFS_pcG9ShDGZw@mail.gmail.com
Whole thread Raw
In response to Re: BUG #16251: ::text type casting of a constant breaks query performance  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
On Sat, Feb 8, 2020 at 11:49 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> so 8. 2. 2020 v 7:44 odesílatel PG Bug reporting form <
> noreply@postgresql.org> napsal:
 
If you can't rearrange things so that the IS NULL argument is seen
as a constant, the UNION trick that Pavel mentioned might be a useful
workaround.  But I'm inclined to think that you need to take two steps
back and figure out whether this query logic is really sane or not.
You do realize that the query is asking to retrieve the entire table,
if whatever-it-is is NULL?  Why would that be what you want?

It is a pretty common tactic to do this.  It much easier on the client side to bind NULL to a parameter when you don't care, rather than dynamically rewrite the query text to remove that condition from it.  Of course that whole thing is likely to be ANDed together with other clauses in an unsimplified real-world example.

Cheers,

Jeff

pgsql-bugs by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: BUG #16252: PL/pgSQL dynamic programming not well suited forworking with different schemas
Next
From: PG Bug reporting form
Date:
Subject: BUG #16253: Documentation bug https://www.postgresql.org/docs/12/auth-methods.html