Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 36D829FA676
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu,  9 Aug 2007 18:11:10 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 75550-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu,  9 Aug 2007 18:11:02 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from ns1.officenet.no (ns1.officenet.no [193.212.174.3])
	by postgresql.org (Postfix) with ESMTP id 511469FA12A
	for <pgsql-sql@postgresql.org>; Thu,  9 Aug 2007 18:11:07 -0300 (ADT)
Received: from [84.48.119.79] (helo=andreak-laptop.local)
	by ns1.officenet.no with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA1:32)
	(Exim 4.63) (envelope-from <andreak@officenet.no>)
	id 1IJFHY-0004t2-PY; Thu, 09 Aug 2007 23:11:04 +0200
From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: Re: PG won't use index on ORDER BY <expression>
Date: Thu, 9 Aug 2007 23:10:52 +0200
User-Agent: KMail/1.9.6
Cc: "Rodrigo De =?utf-8?q?Le=C3=B3n?=" <rdeleonp@gmail.com>
References: <200708092018.44111.andreak@officenet.no>
	<200708092238.46284.andreak@officenet.no>
	<a55915760708091357m4632c952s10bdafd235765941@mail.gmail.com>
In-Reply-To: <a55915760708091357m4632c952s10bdafd235765941@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200708092310.53309.andreak@officenet.no>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/59
X-Sequence-Number: 28997

On Thursday 09 August 2007 22:57:35 Rodrigo De Le=C3=B3n wrote:
> 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=3D0.00..0.08 rows=3D1 width=3D68) (actual time=3D0.030..0.030
> rows=3D0 loops=3D1)
>   ->  Index Scan using person_lowerfullname_idx on person
> (cost=3D0.00..62.25 rows=3D800 width=3D68) (actual time=3D0.018..0.018 ro=
ws=3D0
> loops=3D1)
> 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=3D0.01..5.10 rows=3D1 width=3D68) (actual time=3D0.038..0.038
> rows=3D0 loops=3D1)
>   ->  Index Scan using person_lowerfullname_idx on person
> (cost=3D0.01..20.34 rows=3D4 width=3D68) (actual time=3D0.032..0.032 rows=
=3D0
> loops=3D1)
>         Index Cond: (((lower((COALESCE(firstname, ''::character
> varying))::text) || lower((COALESCE(lastname, ''::character
> varying))::text)) >=3D '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.

I forgot to mention: I use UTF-8. IIRC, it works fine with C-locale without=
=20
the "varchar_pattern_ops", which is why it works for you I think.

=2D-=20
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
=2D-----------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst All=C3=A9 11      | know how to do a thing and to watch         |
PO. Box 529 Sk=C3=B8yen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Tlf:    +47 24 15 38 90 |                                             |
=46ax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
=2D-----------------------+---------------------------------------------+