From: Andreas Joseph Krogh <andreak@officenet.no>
To: pgsql-sql@postgresql.org
Subject: PG won't use index on ORDER BY <expression>
Date: Thu, 9 Aug 2007 20:18:43 +0200
Message-ID: <200708092018.44111.andreak@officenet.no> (raw)
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
person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, '')))
varchar_pattern_ops);
And this query refuses to use that index:
select id from person order by (lower(COALESCE(firstname, '')) ||
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
will use an index if one appropriate exists
--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andreak@officenet.no
Subject: Re: PG won't use index on ORDER BY <expression>
In-Reply-To: <200708092018.44111.andreak@officenet.no>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox