limit 1 and functional indexes

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

Responses

Browse pgsql-performance by date

  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]

Browse pgsql-sql by date

  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]