Thread: PG won't use index on ORDER BY
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 | | ------------------------+---------------------------------------------+
"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
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 | | ------------------------+---------------------------------------------+
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.
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 | | ------------------------+---------------------------------------------+
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 | | ------------------------+---------------------------------------------+
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
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.4on 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 |
notnull 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,
''::charactervarying)::text) || lower(COALESCE(lastname, ''::character varying)::text)))
regression=# explain select id from person order by (lower(COALESCE(firstname, '')) || lower(COALESCE(lastname, '')))
ASClimit 1; QUERY PLAN
-------------------------------------------------------------------------------------------------Limit
(cost=0.00..0.08rows=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
"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