Thread: planner not using index for like operator

-----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.

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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

The col is a varchar. I am currently testing with the inet data type(and
also the ipv4 pgfoundry data type).

Due to time constraints, I am trying to minimize code changes.

What kind of index do I need to create to enable efficient range scans
(e.g anything between 172.16.x.x thru 172.31.x.x) on the inet data type?

Thanks

Sriram

-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

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@pervasive.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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461