From: | arhipov <arhipov(at)dc(dot)baikal(dot)ru> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Postgres does not use indexes with OR-conditions |
Date: | 2014-11-07 04:16:27 |
Message-ID: | 545C479B.7010104@dc.baikal.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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"
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2014-11-07 04:38:13 | Re: Postgres does not use indexes with OR-conditions |
Previous Message | Josh Berkus | 2014-11-06 22:01:38 | Re: pgtune + configurations with 9.3 |