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: | Raw Message | Whole Thread | 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] |