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
