pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPG won't use index on ORDER BY <expression>
9+ messages / 4 participants
[nested] [flat]
* PG won't use index on ORDER BY <expression>
@ 2007-08-09 18:18 Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 9+ messages in thread
From: Andreas Joseph Krogh @ 2007-08-09 18:18 UTC (permalink / raw)
To: pgsql-sql
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PG won't use index on ORDER BY <expression>
@ 2007-08-09 20:00 Gregory Stark <stark@enterprisedb.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 1 reply; 9+ messages in thread
From: Gregory Stark @ 2007-08-09 20:00 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
"Andreas Joseph Krogh" <andreak@officenet.no> writes:
> I create an index:
> CREATE INDEX person_lowerfullname_idx ON
> person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, '')))
> varchar_pattern_ops);
Why are you declaring it using the varchar_pattern_ops?
The default operator set is the one you want for handling ordering. The
pattern_ops operator set is for handling things like x LIKE 'foo%'
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PG won't use index on ORDER BY <expression>
@ 2007-08-09 20:38 Andreas Joseph Krogh <andreak@officenet.no>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 3 replies; 9+ messages in thread
From: Andreas Joseph Krogh @ 2007-08-09 20:38 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: pgsql-sql
On Thursday 09 August 2007 22:00:54 Gregory Stark wrote:
> "Andreas Joseph Krogh" <andreak@officenet.no> writes:
> > I create an index:
> > CREATE INDEX person_lowerfullname_idx ON
> > person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, '')))
> > varchar_pattern_ops);
>
> Why are you declaring it using the varchar_pattern_ops?
>
> The default operator set is the one you want for handling ordering. The
> pattern_ops operator set is for handling things like x LIKE 'foo%'
Ooops, just fugured that out. But - it still doesn't use the index if I remove
the "varchar_pattern_ops". I solved it by adding a function:
CREATE OR REPLACE FUNCTION concat_lower(varchar, varchar) RETURNS varchar AS
$$
SELECT lower(coalesce($1, '')) || lower(coalesce($2, ''))
$$ LANGUAGE SQL IMMUTABLE;
And than creating an index:
CREATE INDEX person_lowerfullname_idx ON person(concat_lower(firstname,
lastname));
Another question then: Why doesn't "varchar_pattern_ops" handle ordering? This
means I need 2 indexes on the columns I want to match with LIKE and ORDER BY.
Just doesn't seem right to need 2 "similar" indexes...
--
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PG won't use index on ORDER BY <expression>
@ 2007-08-09 20:57 Rodrigo De León <rdeleonp@gmail.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
2 siblings, 1 reply; 9+ messages in thread
From: Rodrigo De León @ 2007-08-09 20:57 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PG won't use index on ORDER BY <expression>
@ 2007-08-09 21:10 Andreas Joseph Krogh <andreak@officenet.no>
parent: Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Andreas Joseph Krogh @ 2007-08-09 21:10 UTC (permalink / raw)
To: pgsql-sql; +Cc: Rodrigo De León <rdeleonp@gmail.com>
On Thursday 09 August 2007 22:57:35 Rodrigo De León 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=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.
I forgot to mention: I use UTF-8. IIRC, it works fine with C-locale without
the "varchar_pattern_ops", which is why it works for you I think.
--
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PG won't use index on ORDER BY <expression>
@ 2007-08-09 22:15 Andreas Joseph Krogh <andreak@officenet.no>
parent: Andreas Joseph Krogh <andreak@officenet.no>
2 siblings, 1 reply; 9+ messages in thread
From: Andreas Joseph Krogh @ 2007-08-09 22:15 UTC (permalink / raw)
To: pgsql-sql
On Thursday 09 August 2007 22:38:46 Andreas Joseph Krogh wrote:
> On Thursday 09 August 2007 22:00:54 Gregory Stark wrote:
> > "Andreas Joseph Krogh" <andreak@officenet.no> writes:
> > > I create an index:
> > > CREATE INDEX person_lowerfullname_idx ON
> > > person((lower(COALESCE(firstname, '')) || lower(COALESCE(lastname,
> > > ''))) varchar_pattern_ops);
> >
> > Why are you declaring it using the varchar_pattern_ops?
> >
> > The default operator set is the one you want for handling ordering. The
> > pattern_ops operator set is for handling things like x LIKE 'foo%'
>
> Ooops, just fugured that out. But - it still doesn't use the index if I
> remove the "varchar_pattern_ops". I solved it by adding a function:
>
> CREATE OR REPLACE FUNCTION concat_lower(varchar, varchar) RETURNS varchar
> AS $$
> SELECT lower(coalesce($1, '')) || lower(coalesce($2, ''))
> $$ LANGUAGE SQL IMMUTABLE;
>
> And than creating an index:
> CREATE INDEX person_lowerfullname_idx ON person(concat_lower(firstname,
> lastname));
>
> Another question then: Why doesn't "varchar_pattern_ops" handle ordering?
> This means I need 2 indexes on the columns I want to match with LIKE and
> ORDER BY. Just doesn't seem right to need 2 "similar" indexes...
Hmm, one more question:
If I want to ORDER BY "created" too, the index is not used anymore:
-- This uses index:
EXPLAIN ANALYZE select firstname, lastname from person order by
concat_lower(firstname, lastname) ASC limit 10;
-- This doesn't
EXPLAIN ANALYZE select firstname, lastname from person order by
concat_lower(firstname, lastname) ASC, created DESC limit 10;
I figured out that it's because they have different sort-order (ASC/DESC). If
both are ASC, the index is used.
Is there a way around this?
--
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 | |
------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PG won't use index on ORDER BY <expression>
@ 2007-08-09 23:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2007-08-09 23:35 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
Andreas Joseph Krogh <andreak@officenet.no> writes:
>> Another question then: Why doesn't "varchar_pattern_ops" handle ordering?
It does handle ordering, just not the ordering you're asking for here.
If you substituted USING ~<~ for ASC you'd find that the pattern_ops
index could be used for that.
>> This means I need 2 indexes on the columns I want to match with LIKE and
>> ORDER BY. Just doesn't seem right to need 2 "similar" indexes...
If you want to use the same index for both, you have to run the database
in C locale. Non-C locales generally define a sort ordering that is not
compatible with LIKE searches. (The point of the pattern_ops opclass is
really to force C-locale ordering of the index when the ordinary text
comparison operators yield a different ordering.)
> -- This doesn't
> EXPLAIN ANALYZE select firstname, lastname from person order by
> concat_lower(firstname, lastname) ASC, created DESC limit 10;
This ORDER BY is asking for an ordering that is almost completely
unrelated to the index's ordering.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PG won't use index on ORDER BY <expression>
@ 2007-08-10 01:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andreas Joseph Krogh <andreak@officenet.no>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2007-08-10 01:35 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql@postgresql.org, "Rodrigo De León" <rdeleonp@gmail.com>
Andreas Joseph Krogh <andreak@officenet.no> writes:
> I forgot to mention: I use UTF-8. IIRC, it works fine with C-locale without
> the "varchar_pattern_ops", which is why it works for you I think.
That shouldn't make any difference, and doesn't for me in testing here:
regression=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-13)
(1 row)
regression=# show lc_collate;
lc_collate
------------
en_US.utf8
(1 row)
regression=# show server_encoding ;
server_encoding
-----------------
UTF8
(1 row)
regression=# \d person
Table "public.person"
Column | Type | Modifiers
-----------+-------------------+-----------------------------------------------------
id | integer | not null default nextval('person_id_seq'::regclass)
firstname | character varying |
lastname | character varying |
Indexes:
"person_pkey" PRIMARY KEY, btree (id)
"person_lowerfullname_idx" btree ((lower(COALESCE(firstname, ''::character varying)::text) || lower(COALESCE(lastname, ''::character varying)::text)))
regression=# explain select id from person order by (lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, ''))) ASC limit 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.08 rows=1 width=68)
-> Index Scan using person_lowerfullname_idx on person (cost=0.00..62.25 rows=800 width=68)
(2 rows)
So there's something going on that you haven't told us about your installation.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PG won't use index on ORDER BY <expression>
@ 2007-08-10 03:10 Gregory Stark <stark@enterprisedb.com>
parent: Andreas Joseph Krogh <andreak@officenet.no>
2 siblings, 0 replies; 9+ messages in thread
From: Gregory Stark @ 2007-08-10 03:10 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreak@officenet.no>; +Cc: pgsql-sql
"Andreas Joseph Krogh" <andreak@officenet.no> writes:
> Ooops, just fugured that out. But - it still doesn't use the index if I remove
> the "varchar_pattern_ops". I solved it by adding a function:
Hm, well it does for me, you would have to post your explain analyze to see
what's going on.
> Another question then: Why doesn't "varchar_pattern_ops" handle ordering? This
> means I need 2 indexes on the columns I want to match with LIKE and ORDER BY.
> Just doesn't seem right to need 2 "similar" indexes...
If you initd in the C locale you only need one index. In other locales the
collation order and the pattern order are different.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-08-10 03:10 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-09 18:18 PG won't use index on ORDER BY <expression> Andreas Joseph Krogh <andreak@officenet.no>
2007-08-09 20:00 ` Gregory Stark <stark@enterprisedb.com>
2007-08-09 20:38 ` Andreas Joseph Krogh <andreak@officenet.no>
2007-08-09 20:57 ` Rodrigo De León <rdeleonp@gmail.com>
2007-08-09 21:10 ` Andreas Joseph Krogh <andreak@officenet.no>
2007-08-10 01:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-09 22:15 ` Andreas Joseph Krogh <andreak@officenet.no>
2007-08-09 23:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-10 03:10 ` Gregory Stark <stark@enterprisedb.com>
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