Reply-To: <alexandra@trymedia.com>
From: "Alexandra Birch" <alexandra@trymedia.com>
To: <pgsql-sql@postgresql.org>
Subject: limit 1 and functional indexes
Date: Wed, 28 Jan 2004 12:23:38 +0100
Message-ID: <BJELKOAELOIHMLJIEGHJGENNENAA.alexandra@trymedia.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Importance: Normal

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