Skip site navigation (1) Skip section navigation (2)

Re: planner not using index for like operator

From: "Sriram Dandapani" <sdandapani(at)counterpane(dot)com>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: "Pgsql-Performance \(E-mail\)" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: planner not using index for like operator
Date: 2006-04-25 18:52:19
Message-ID: 6992E470F12A444BB787B5C937B9D4DF0406B27D@ca-mail1.cis.local (view raw or flat)
Thread:
Lists: pgsql-performance
Using an index on col1 with the operator class varchar_pattern_ops , I
was able to get a 3 second response time. That will work for me.
I used a like '172.%' and an extra pattern matching condition to
restrict
Between 172.16.x.x and 172.31.x.x

Thanks for the input..I will also test the inet data type to see if
there are differences.

Sriram

-----Original Message-----
From: Jim C. Nasby [mailto:jnasby(at)pervasive(dot)com] 
Sent: Tuesday, April 25, 2006 11:25 AM
To: Sriram Dandapani
Cc: Pgsql-Performance (E-mail)
Subject: Re: [PERFORM] planner not using index for like operator

On Tue, Apr 25, 2006 at 10:08:02AM -0700, Sriram Dandapani wrote:
Here's the key:

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

In order to do a like comparison, it has to convert col1 (which I'm
guessing is of type 'inet') to text, so there's no way it can use an
index on col1 (maybe a function index, but that's a different story).

Is there some reason you're not doing

WHERE col1 <<= '172/8'::inet

?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2006-04-25 19:12:20
Subject: Re: Large (8M) cache vs. dual-core CPUs
Previous:From: Joshua D. DrakeDate: 2006-04-25 18:49:37
Subject: Re: Large (8M) cache vs. dual-core CPUs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group