| From: | "Alexandra Birch" <alexandra(at)trymedia(dot)com> |
|---|---|
| To: | <pgsql-sql(at)postgresql(dot)org> |
| Subject: | limit 1 and functional indexes |
| Date: | 2004-01-28 11:23:38 |
| Message-ID: | BJELKOAELOIHMLJIEGHJGENNENAA.alexandra@trymedia.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bill Moran | 2004-01-28 16:02:21 | Re: On the performance of views |
| Previous Message | Stef | 2004-01-28 10:47:22 | Re: postgres timeout. [SOLVED] |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Przemysław Słupkowski | 2004-01-28 13:39:11 | managing users in postgresql 7.4.1 |
| Previous Message | Stef | 2004-01-28 10:47:22 | Re: postgres timeout. [SOLVED] |