Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id E4FE99FB865
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu,  9 Aug 2007 17:57:41 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 57145-01 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu,  9 Aug 2007 17:57:28 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from nz-out-0506.google.com (nz-out-0506.google.com
 [64.233.162.234])
	by postgresql.org (Postfix) with ESMTP id 4890C9FB818
	for <pgsql-sql@postgresql.org>; Thu,  9 Aug 2007 17:57:37 -0300 (ADT)
Received: by nz-out-0506.google.com with SMTP id l8so287450nzf
	for <pgsql-sql@postgresql.org>; Thu, 09 Aug 2007 13:57:36 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=MRVaXSSzjyNaLjtnZeDOEioC53nVUkbuoOTgvT3xxPX8GKd7lJuHIxeXX/f7aSgVd5fihxjMZDjYiOk87/qCIcVn9HyHhF7xPDLpaIJZt/c5ZwUlc7dzHpNQyCqroo9JDQylGnNa2R/EIDg++Ryp6U0oroRBzXN/qHXm0bulPlM=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=lQ5kELOyWZawMWrjk6aIGdfsxsCZsHJ81VqSpiiFJhTtD0s6Wf58GCFbIw9DhUMsDLap3eealk2BvF/96dcMXpsvnjKFSxoERIXLZ+zRdEWd5zI8DwfrDpRHYhOzRO3K5yUnrn8Dzm4Wj6zK+Dz6qo5bbE+q0P5UzvnCzSLg1ZQ=
Received: by 10.100.173.19 with SMTP id v19mr1660500ane.1186693055896;
	Thu, 09 Aug 2007 13:57:35 -0700 (PDT)
Received: by 10.100.33.20 with HTTP; Thu, 9 Aug 2007 13:57:35 -0700 (PDT)
Message-ID: <a55915760708091357m4632c952s10bdafd235765941@mail.gmail.com>
Date: Thu, 9 Aug 2007 15:57:35 -0500
From: "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <rdeleonp@gmail.com>
To: "Andreas Joseph Krogh" <andreak@officenet.no>
Subject: Re: PG won't use index on ORDER BY <expression>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200708092238.46284.andreak@officenet.no>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <200708092018.44111.andreak@officenet.no>
	<87fy2s1nrd.fsf@oxford.xeocode.com>
	<200708092238.46284.andreak@officenet.no>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/58
X-Sequence-Number: 28996

On 8/9/07, Andreas Joseph Krogh <andreak@officenet.no> wrote:
> Ooops, just fugured that out. But - it still doesn't use the index if I remove
> the "varchar_pattern_ops".

Huh?

CREATE INDEX person_lowerfullname_idx
ON person
((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))));


EXPLAIN ANALYZE select id from person order by
(lower(COALESCE(firstname, '')) ||
lower(COALESCE(lastname, ''))) ASC limit 1;

Limit  (cost=0.00..0.08 rows=1 width=68) (actual time=0.030..0.030
rows=0 loops=1)
  ->  Index Scan using person_lowerfullname_idx on person
(cost=0.00..62.25 rows=800 width=68) (actual time=0.018..0.018 rows=0
loops=1)
Total runtime: 0.318 ms


EXPLAIN ANALYZE select id from person
where (lower(COALESCE(firstname, '')) ||
lower(COALESCE(lastname, ''))) like 'A%'
order by (lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, '')))
ASC limit 1;

Limit  (cost=0.01..5.10 rows=1 width=68) (actual time=0.038..0.038
rows=0 loops=1)
  ->  Index Scan using person_lowerfullname_idx on person
(cost=0.01..20.34 rows=4 width=68) (actual time=0.032..0.032 rows=0
loops=1)
        Index Cond: (((lower((COALESCE(firstname, ''::character
varying))::text) || lower((COALESCE(lastname, ''::character
varying))::text)) >= 'A'::text) AND ((lower((COALESCE(firstname,
''::character varying))::text) || lower((COALESCE(lastname,
''::character varying))::text)) < 'B'::text))
        Filter: ((lower((COALESCE(firstname, ''::character
varying))::text) || lower((COALESCE(lastname, ''::character
varying))::text)) ~~ 'A%'::text)
Total runtime: 0.138 ms


Works for me.