Обсуждение: "Group By " index usage
I have a table with this index:
create index ARTISTS_NAME on ARTISTS (
lower(AR_NAME)
);
Te index is over a colum with this definition:
AR_NAME VARCHAR(256) null,
I want to optimize this query:
select * from artists where lower(ar_name) like
lower('a%') order by lower(ar_name) limit 20;
I think the planner should use the index i have. But
the result of the explain command is:
explain analyze select * from artists where
lower(ar_name) like lower('a%') order by
lower(ar_name) limit 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=20420.09..20420.14 rows=20 width=360)
(actual time=2094.13..2094.19 rows=20 loops=1)
-> Sort (cost=20420.09..20433.52 rows=5374
width=360) (actual time=2094.13..2094.16 rows=21
loops=1)
Sort Key: lower((ar_name)::text)
-> Index Scan using artists_name on artists
(cost=0.00..19567.09 rows=5374 width=360) (actual
time=0.11..1391.97 rows=59047 loops=1)
Index Cond: ((lower((ar_name)::text) >=
'a'::text) AND (lower((ar_name)::text) < 'b'::text))
Filter: (lower((ar_name)::text) ~~
'a%'::text)
Total runtime: 2098.62 msec
(7 rows)
The "ORDER BY" clause is not using the index!. I don't
know why.
I have the locale configured to C, and the index works
well with the "like" operator.
¿Could you help me? I am really lost.
______________________________________________
Renovamos el Correo Yahoo!: ¡100 MB GRATIS!
Nuevos servicios, más seguridad
http://correo.yahoo.es
sdfasdfas sdfasdfs <descuarinjado@yahoo.es> writes:
> I have a table with this index:
> create index ARTISTS_NAME on ARTISTS (
> lower(AR_NAME)
> );
> Te index is over a colum with this definition:
> AR_NAME VARCHAR(256) null,
> I want to optimize this query:
> select * from artists where lower(ar_name) like
> lower('a%') order by lower(ar_name) limit 20;
> I think the planner should use the index i have.
Update to 7.4, or declare the column as TEXT instead of VARCHAR.
Older versions aren't very bright about situations involving
implicit coercions.
regards, tom lane
Did you test with ILIKE instead of lower LIKE lower ?
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of sdfasdfas
sdfasdfs
Sent: mercredi 24 novembre 2004 18:37
To: pgsql-performance@postgresql.org
Subject: [PERFORM] "Group By " index usage
I have a table with this index:
create index ARTISTS_NAME on ARTISTS (
lower(AR_NAME)
);
Te index is over a colum with this definition:
AR_NAME VARCHAR(256) null,
I want to optimize this query:
select * from artists where lower(ar_name) like
lower('a%') order by lower(ar_name) limit 20;
I think the planner should use the index i have. But the result of the
explain command is:
explain analyze select * from artists where
lower(ar_name) like lower('a%') order by
lower(ar_name) limit 20;
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------------------------------------
Limit (cost=20420.09..20420.14 rows=20 width=360) (actual
time=2094.13..2094.19 rows=20 loops=1)
-> Sort (cost=20420.09..20433.52 rows=5374
width=360) (actual time=2094.13..2094.16 rows=21
loops=1)
Sort Key: lower((ar_name)::text)
-> Index Scan using artists_name on artists
(cost=0.00..19567.09 rows=5374 width=360) (actual
time=0.11..1391.97 rows=59047 loops=1)
Index Cond: ((lower((ar_name)::text) >=
'a'::text) AND (lower((ar_name)::text) < 'b'::text))
Filter: (lower((ar_name)::text) ~~
'a%'::text)
Total runtime: 2098.62 msec
(7 rows)
The "ORDER BY" clause is not using the index!. I don't know why.
I have the locale configured to C, and the index works well with the "like"
operator.
¿Could you help me? I am really lost.
______________________________________________
Renovamos el Correo Yahoo!: ¡100 MB GRATIS!
Nuevos servicios, más seguridad
http://correo.yahoo.es
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend