Re: BUG #16251: ::text type casting of a constant breaks query performance

so 8. 2. 2020 v 17:49 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > so 8. 2. 2020 v 7:44 odesílatel PG Bug reporting form <
> > noreply(at)postgresql(dot)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?
>

If I remember well, this technique was a trick to use one query for
variables that can be (or should not be) specified by user.

I can has a variable $ID. If user specifies this variable, it has some
number, else it has NULL.

When you want to use one query for both possibilities (static query), then
you can write

SELECT * FROM tab WHERE ($ID is NULL OR id = $ID)

We used this technique 20 years ago, and I think it was very popular, but
databases was significantly smaller, and only few people had good knowledge
of SQL databases.

> regards, tom lane
>