Message-ID: <545C479B.7010104@dc.baikal.ru>
Date: Fri, 07 Nov 2014 12:16:27 +0800
From: arhipov <arhipov@dc.baikal.ru>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.2.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Postgres does not use indexes with OR-conditions
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hello,

I have just came across interesting Postgres behaviour with 
OR-conditions. Are there any chances that the optimizer will handle this 
situation in the future?

select *
from commons.financial_documents fd
where fd.creation_time <= '2011-11-07 10:39:07.285022+08'
order by fd.creation_time desc
limit 200

"Limit  (cost=4.30..44.50 rows=200 width=67) (actual time=0.032..1.376 
rows=200 loops=1)"
"  ->  Index Scan Backward using financial_document_creation_time_index 
on financial_documents fd (cost=4.30..292076.25 rows=1453075 width=67) 
(actual time=0.027..0.683 rows=200 loops=1)"
"        Index Cond: (creation_time <= '2011-11-07 
11:39:07.285022+09'::timestamp with time zone)"
"Total runtime: 1.740 ms"

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'
order by fd.creation_time desc
limit 200

"Limit  (cost=4.30..71.76 rows=200 width=67) (actual 
time=1067.935..1069.126 rows=200 loops=1)"
"  ->  Index Scan Backward using financial_document_creation_time_index 
on financial_documents fd (cost=4.30..490104.07 rows=1453075 width=67) 
(actual time=1067.927..1068.532 rows=200 loops=1)"
"        Filter: ((creation_time = '2011-11-07 
11:39:07.285022+09'::timestamp with time zone) OR (creation_time < 
'2011-11-07 11:39:07.285022+09'::timestamp with time zone))"
"        Rows Removed by Filter: 776785"
"Total runtime: 1069.480 ms"


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance