From: | Artūras Lapinskas <arturaslape(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Index order ignored after `is null` in query |
Date: | 2014-11-07 11:14:51 |
Message-ID: | 20141107111451.GA477@guest-docking-cx-1-0139.ethz.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi,
thanks for your time and answer. Not treating IS NULL as equality
operator definitely helps me to make more sense out of previous
explains.
--
Best Regard,
Artūras Lapinskas
On Thu, Nov 06, 2014 at 12:23:12PM -0500, Tom Lane wrote:
>=?utf-8?Q?Art=C5=ABras?= Lapinskas <arturaslape(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2014-11-07 13:55:32 | Re: Postgres does not use indexes with OR-conditions |
Previous Message | Vlad Arkhipov | 2014-11-07 05:06:26 | Re: Postgres does not use indexes with OR-conditions |