Re: Index order ignored after `is null` in query

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@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