From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: pgsql-sql@postgresql.org
Subject: PG won't use index on ORDER BY <expression>
Date: Thu, 9 Aug 2007 20:18:43 +0200
User-Agent: KMail/1.9.6
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200708092018.44111.andreak@officenet.no>

Hi all!

I have problems getting PG to use an index when sorting.
I have a simple table

create table person(
id serial primary key,
firstname varchar,
lastname varchar
);

I create an index:
CREATE INDEX person_lowerfullname_idx ON=20
person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, '')))=20
varchar_pattern_ops);

And this query refuses to use that index:
select id from person order by (lower(COALESCE(firstname, '')) ||=20
lower(COALESCE(lastname, ''))) ASC limit 1;

If I add an index:
CREATE INDEX person_lowerfirstname_idx ON person(lower(firstname));

The following query will use that index for sorting and cut-off:
select id from person order by (lower(firstname) ) ASC limit 1;

Any hints or explaination on why the "concat-index" won't be used?

PS: I have tried to issue a "set enable_seqscan to off;" to ensure that it=
=20
will use an index if one appropriate exists

=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=E9 11      | know how to do a thing and to watch         |
PO. Box 529 Sk=F8yen      | 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-----------------------+---------------------------------------------+