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

От: Artūras Lapinskas
Тема: Re: Index order ignored after `is null` in query
Дата: ,
Msg-id: 20141107111451.GA477@guest-docking-cx-1-0139.ethz.ch
(см: обсуждение, исходный текст)
Ответ на: Re: Index order ignored after `is null` in query  (Tom Lane)
Ответы: Re: Index order ignored after `is null` in query  (Jim Nasby)
Список: pgsql-performance

Скрыть дерево обсуждения

Index order ignored after `is null` in query  (Artūras Lapinskas, )
 Re: Index order ignored after `is null` in query  (Artūras Lapinskas, )
  Re: Index order ignored after `is null` in query  (Tom Lane, )
   Re: Index order ignored after `is null` in query  (Artūras Lapinskas, )
    Re: Index order ignored after `is null` in query  (Jim Nasby, )

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



В списке pgsql-performance по дате сообщения:

От: David G Johnston
Дата:
Сообщение: Re: Postgres does not use indexes with OR-conditions
От: Ruben Domingo Gaspar Aparicio
Дата:
Сообщение: Postgres slave not catching up (on 9.2)