X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 14D9B3A53D7 for ; Tue, 30 Nov 2004 16:05:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77651-05 for ; Tue, 30 Nov 2004 16:05:07 +0000 (GMT) Received: from mailer.unicite.fr.netcentrex.net (mailer.fr.netcentrex.net [62.161.167.249]) by svr1.postgresql.org (Postfix) with ESMTP id C12903A45FE for ; Tue, 30 Nov 2004 16:05:04 +0000 (GMT) Received: from akira ([192.168.101.228]) by mailer.unicite.fr.netcentrex.net with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id 42CY1482; Tue, 30 Nov 2004 17:05:02 +0100 From: "Alban Medici (NetCentrex)" To: Subject: Re: "Group By " index usage Date: Tue, 30 Nov 2004 17:04:58 +0100 Organization: NetCentrex MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook, Build 11.0.6353 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Thread-Index: AcTSTV0hpVsTKePKSd2jm5XGApe98wEqOQSQ In-Reply-To: <20041124173659.2441.qmail@web54301.mail.yahoo.com> Message-Id: <20041130160504.C12903A45FE@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/421 X-Sequence-Number: 9375 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; =20 QUERY PLAN =20 -------------------------------------------------------------------------= --- --------------------------------------------------------------------- Limit (cost=3D20420.09..20420.14 rows=3D20 width=3D360) (actual time=3D2094.13..2094.19 rows=3D20 loops=3D1) -> Sort (cost=3D20420.09..20433.52 rows=3D5374 width=3D360) (actual time=3D2094.13..2094.16 rows=3D21 loops=3D1) Sort Key: lower((ar_name)::text) -> Index Scan using artists_name on artists (cost=3D0.00..19567.09 rows=3D5374 width=3D360) (actual time=3D0.11..1391.97 rows=3D59047 loops=3D1) Index Cond: ((lower((ar_name)::text) >=3D '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.=20 =BFCould you help me? I am really lost.=20 =09 ______________________________________________ Renovamos el Correo Yahoo!: =A1100 MB GRATIS! Nuevos servicios, m=E1s seguridad http://correo.yahoo.es ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend