X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7AD43D1D26C for ; Thu, 29 Jan 2004 15:10:51 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 62764-10 for ; Thu, 29 Jan 2004 11:10:29 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 048B6D1D1D8 for ; Thu, 29 Jan 2004 11:10:19 -0400 (AST) Received: (qmail 25117 invoked by uid 500); 29 Jan 2004 15:11:46 -0000 Date: Thu, 29 Jan 2004 09:11:46 -0600 From: Bruno Wolff III To: Alexandra Birch Cc: pgsql-sql@postgresql.org Subject: Re: limit 1 and functional indexes Message-ID: <20040129151146.GB6865@wolff.to> Mail-Followup-To: Alexandra Birch , pgsql-sql@postgresql.org References: <20040129125240.GA17068@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/264 X-Sequence-Number: 16657 On Thu, Jan 29, 2004 at 16:02:06 +0100, Alexandra Birch wrote: > > Here is the output of EXPLAIN ANALYZE first with limit 1 then without: The time estimate for the limit 1 case is way off. I can't tell if that is a bug or not having detailed enough statistics. Hopefully someone more knowlegable will take a look at this question. > > explain analyze > select code > from transactions > where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111') > order by order_date DESC LIMIT 1; > -------------------------------------------------------------------------------------------------- > Limit (cost=0.00..332.44 rows=1 width=33) (actual time=377745.75..377745.75 rows=0 loops=1) > -> Index Scan Backward using transactions_date_aff on transactions (cost=0.00..982549.96 rows=2956 width=33) (actual > time=377718.61..377718.61 rows=0 loops=1) > Filter: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text) > Total runtime: 378439.32 msec > > explain analyze > select code > from transactions > where UPPER(pop) = UPPER('79bcdc8a4a4f99e7c111111111111111') > order by order_date DESC; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------ > ------------- > Sort (cost=11824.16..11831.55 rows=2956 width=33) (actual time=248.17..248.17 rows=0 loops=1) > Sort Key: order_date > -> Index Scan using transactions_pop_i on transactions (cost=0.00..11653.79 rows=2956 width=33) (actual time=126.13..126.13 > rows=0 loops=1) > Index Cond: (upper((pop)::text) = '79BCDC8A4A4F99E7C111111111111111'::text) > Total runtime: 248.25 msec > > Thank you, > > Alexandra >