pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: imyfess@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16251: ::text type casting of a constant breaks query performance
Date: Sat, 08 Feb 2020 11:49:13 -0500
Message-ID: <10694.1581180553@sss.pgh.pa.us> (raw)
In-Reply-To: <CAFj8pRAADe1n7GhBXZevEf2kTZ0r=RfZBA5-No0zcQhjOY88jw@mail.gmail.com>
References: <16251-51e254d9a8bacbb2@postgresql.org>
	<CAFj8pRAADe1n7GhBXZevEf2kTZ0r=RfZBA5-No0zcQhjOY88jw@mail.gmail.com>

Pavel Stehule <pavel.stehule@gmail.com> writes:
> so 8. 2. 2020 v 7:44 odesílatel PG Bug reporting form <
> noreply@postgresql.org> napsal:
>> With '::text' type casting of '2020-02-08' (which is already text) query
>> permofance is very low

> The '2020-02-08' is not text type - it is 'unknown' type - and then is just
> directly transformed to date. I think so cast from text to date is not
> maybe immutable, and it can stops some optimizations.

Yeah.  There actually isn't any cast from text to date, if you look into
pg_cast.  So "('2020-02-08'::text)::date" is implemented as a text Const
that's fed through a CoerceViaIO node that applies date_in(), and
date_in() is only stable not immutable.  (That must be so because its
behavior depends on the DateStyle setting, and maybe TimeZone too; not
sure about the latter but definitely the former.)  So the planner is
unable to reduce the IS NULL test to constant-false and thereby get
rid of the OR, and that means it can't usefully apply the index.

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?

			regards, tom lane





view thread (6+ messages)  latest in thread

Message-ID: <10694.1581180553@sss.pgh.pa.us>
Permalink:  ../10694.1581180553@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/10694.1581180553@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, pavel.stehule@gmail.com
  Subject: Re: BUG #16251: ::text type casting of a constant breaks query performance
  In-Reply-To: <10694.1581180553@sss.pgh.pa.us>
  References: <16251-51e254d9a8bacbb2@postgresql.org> <CAFj8pRAADe1n7GhBXZevEf2kTZ0r=RfZBA5-No0zcQhjOY88jw@mail.gmail.com> <10694.1581180553@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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