From: Tom Lane <tgl@sss.pgh.pa.us>
To: Artūras Lapinskas <arturaslape@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Index order ignored after `is null` in query
Date: Thu, 06 Nov 2014 12:23:12 -0500
Message-ID: <10744.1415294592@sss.pgh.pa.us> (raw)
In-Reply-To: <20141106170612.GA527@public-docking-cx-1226.ethz.ch>
References: <20141105214243.GA546@public-docking-cx-1771.ethz.ch>
<20141106170612.GA527@public-docking-cx-1226.ethz.ch>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
=?utf-8?Q?Art=C5=ABras?= Lapinskas <arturaslape@gmail.com> writes:
> After some more investigation my wild guess would be that then nulls are
> involved in query postgresql wants to double check whatever they are
> really nulls in actual relation (maybe because of dead tuples).
No, it's much simpler than that: IS NULL is not an equality operator,
so it's not treated as constraining sort order.
What you're asking for amounts to building in an assumption that "all
nulls are equal", which is exactly not what the SQL semantics for NULL
say. So I feel that you have probably chosen a bogus data design
that is misusing NULL for a purpose at variance with the SQL semantics.
That's likely to bite you on the rear in many more ways than this.
Even disregarding the question of whether it's semantically appropriate,
getting the planner to handle IS NULL this way would be a significant
amount of work.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, arturaslape@gmail.com
Subject: Re: Index order ignored after `is null` in query
In-Reply-To: <10744.1415294592@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