Re: Postgres does not use indexes with OR-conditions

От: Andrew Dunstan
Тема: Re: Postgres does not use indexes with OR-conditions
Дата: ,
Msg-id: 545CCF54.1010205@dunslane.net
(см: обсуждение, исходный текст)
Ответ на: Re: Postgres does not use indexes with OR-conditions  (Vlad Arkhipov)
Ответы: Re: Postgres does not use indexes with OR-conditions  (Kevin Grittner)
Список: pgsql-performance

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

Postgres does not use indexes with OR-conditions  (arhipov, )
 Re: Postgres does not use indexes with OR-conditions  (David Rowley, )
  Re: Postgres does not use indexes with OR-conditions  (Vlad Arkhipov, )
   Re: Postgres does not use indexes with OR-conditions  (Andrew Dunstan, )
    Re: Postgres does not use indexes with OR-conditions  (Kevin Grittner, )
     Re: Postgres does not use indexes with OR-conditions  (Tom Lane, )
     Re: Postgres does not use indexes with OR-conditions  (David G Johnston, )

On 11/07/2014 12:06 AM, Vlad Arkhipov wrote:
> It was just a minimal example. The real query looks like this.
>
> select *
> from commons.financial_documents fd
> where fd.creation_time < '2011-11-07 10:39:07.285022+08'
>   or (fd.creation_time = '2011-11-07 10:39:07.285022+08' and
> fd.financial_document_id < 100)
> order by fd.creation_time desc
> limit 200
>
> I need to rewrite it in the way below to make Postgres use the index.
>
> select *
> from commons.financial_documents fd
> where fd.creation_time <= '2011-11-07 10:39:07.285022+08'
>   and (
>     fd.creation_time < '2011-11-07 10:39:07.285022+08'
>       or (fd.creation_time = '2011-11-07 10:39:07.285022+08' and
> fd.financial_document_id < 100)
>   )
> order by fd.creation_time desc
> limit 200
>

First, please do not top-post on the PostgreSQL lists. See
<http://idallen.com/topposting.html>

Second, the last test for fd.creation_time in your query seems
redundant. Could you not rewrite it as something this?:

    where fd.creation_time <= '2011-11-07 10:39:07.285022+08'
         and (fd.creation_time < '2011-11-07 10:39:07.285022+08'
              or fd.financial_document_id < 100)

cheers

andrew



В списке 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)