X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 766A211F6097 for ; Tue, 25 Apr 2006 15:03:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 62649-08 for ; Tue, 25 Apr 2006 15:03:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from globalrelay.com (mail1.globalrelay.com [216.18.71.77]) by postgresql.org (Postfix) with ESMTP id 3653911F6093 for ; Tue, 25 Apr 2006 15:03:15 -0300 (ADT) X-Virus-Scanned: Scanned by GRC-AntiVirus Gateway X-GR-Acctd: YES Received: from [67.90.96.2] (HELO DaveEMachine) by globalrelay.com (CommuniGate Pro SMTP 4.2.3) with ESMTP id 88648296; Tue, 25 Apr 2006 11:03:14 -0700 From: "Dave Dutcher" To: "'Sriram Dandapani'" , "'Pgsql-Performance \(E-mail\)'" Subject: Re: planner not using index for like operator Date: Tue, 25 Apr 2006 13:03:13 -0500 Message-ID: <000001c66892$8583d040$8300a8c0@tridecap.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C66868.9CB03940" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 Importance: Normal In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF0406B241@ca-mail1.cis.local> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/558 X-Sequence-Number: 18642 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C66868.9CB03940 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit If you are using a locale other than the C locale, you need to create the index with an operator class to get index scans with like. See here for details: http://www.postgresql.org/docs/8.1/interactive/indexes-opclass.html -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Sriram Dandapani Sent: Tuesday, April 25, 2006 12:08 PM To: Pgsql-Performance (E-mail) Subject: [PERFORM] planner not using index for like operator For the query Select col1 from table1 Where col1 like '172.%' The table has 133 million unique ip addresses. Col1 is indexed. The optimizer is using a sequential scan This is the explain analyze output "Seq Scan on table1 (cost=0.00..2529284.80 rows=1 width=15) (actual time=307591.339..565251.775 rows=524288 loops=1)" " Filter: ((col1)::text ~~ '172.%'::text)" "Total runtime: 565501.873 ms" The number of affected rows (500K) is a small fraction of the total row count. ------=_NextPart_000_0001_01C66868.9CB03940 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message
If you=20 are using a locale other than the C locale, you need to create the index = with an=20 operator class to get index scans with like.
 
See=20 here for details:
 
http://www.postgresql.org/docs/8.1/interactive/indexes-opclass.html
 
-----Original Message-----
From:=20 pgsql-performance-owner@postgresql.org=20 [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of = Sriram=20 Dandapani
Sent: Tuesday, April 25, 2006 12:08 = PM
To:=20 Pgsql-Performance (E-mail)
Subject: [PERFORM] planner not = using=20 index for like operator

For the=20 query

 

 

Select col1 from=20 table1

Where col1 like=20 ‘172.%’

 

The table has 133 = million unique=20 ip addresses. Col1 is indexed.

 

The optimizer is using a = sequential scan

 

This is the explain = analyze=20 output

 

"Seq Scan on table1=20 (cost=3D0.00..2529284.80 rows=3D1 width=3D15) (actual = time=3D307591.339..565251.775=20 rows=3D524288 loops=3D1)"

"  Filter: = ((col1)::text ~~=20 '172.%'::text)"

"Total runtime: = 565501.873=20 ms"

 

 

The number of affected = rows (500K)=20 is a small fraction of the total row=20 count.

------=_NextPart_000_0001_01C66868.9CB03940--