Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j0TIK-0007gy-K1 for pgsql-bugs@arkaria.postgresql.org; Sat, 08 Feb 2020 16:49:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j0TIH-0004iW-G6 for pgsql-bugs@arkaria.postgresql.org; Sat, 08 Feb 2020 16:49:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j0TIG-0004iP-Vp for pgsql-bugs@lists.postgresql.org; Sat, 08 Feb 2020 16:49:21 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1j0TID-00055t-RQ for pgsql-bugs@lists.postgresql.org; Sat, 08 Feb 2020 16:49:19 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id 018GnDEj010695; Sat, 8 Feb 2020 11:49:13 -0500 From: Tom Lane To: Pavel Stehule cc: imyfess@gmail.com, PostgreSQL mailing lists Subject: Re: BUG #16251: ::text type casting of a constant breaks query performance In-reply-to: References: <16251-51e254d9a8bacbb2@postgresql.org> Comments: In-reply-to Pavel Stehule message dated "Sat, 08 Feb 2020 08:09:11 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <10693.1581180553.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Sat, 08 Feb 2020 11:49:13 -0500 Message-ID: <10694.1581180553@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Pavel Stehule 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