limit 1 and functional indexes

Поиск
Список
Период
Сортировка
От Alexandra Birch
Тема limit 1 and functional indexes
Дата
Msg-id BJELKOAELOIHMLJIEGHJGENNENAA.alexandra@trymedia.com
обсуждение исходный текст
Ответы Re: limit 1 and functional indexes
Список pgsql-sql
Hi, 

Postgres choses the wrong index when I add limit 1 to the query.
This should not affect the index chosen.
I read that functional indexes are sometimes not chosen correctly by 
optimizer. 
Is there anything I can do to always use the functional index in the
following queries? 

Query with limit 1 choses wrong index:
---------------------------------------------------------------------------------------
explain
select code 
from transactions 
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
order by order_date DESC LIMIT 1

Index Scan Backward using transactions_date_aff on transactions (cost=0.00..930780.96 rows=2879 width=33)
---------------------------------------------------------------------------------------

Without limit 1 choses correct index:
---------------------------------------------------------------------------------------
explain
select code 
from transactions 
where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111')
order by order_date DESC

Index Scan using transactions_pop_i on transactions  (cost=0.00..11351.72 rows=2879 width=33)
---------------------------------------------------------------------------------------

We have postgresql-7.3.2-3.
Thank you,

Alexandra


В списке pgsql-sql по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Aggregate function error in 7.4
Следующее
От: Stef
Дата:
Сообщение: postgres timeout.