pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedplanner not using index for like operator
5+ messages / 3 participants
[nested] [flat]
* planner not using index for like operator
@ 2006-04-25 17:08 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 2 replies; 5+ messages in thread
From: Sriram Dandapani @ 2006-04-25 17:08 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: planner not using index for like operator
@ 2006-04-25 18:03 Dave Dutcher <dave@tridecap.com>
parent: Sriram Dandapani <sdandapani@counterpane.com>
1 sibling, 0 replies; 5+ messages in thread
From: Dave Dutcher @ 2006-04-25 18:03 UTC (permalink / raw)
To: 'Sriram Dandapani' <sdandapani@counterpane.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: planner not using index for like operator
@ 2006-04-25 18:24 Jim C. Nasby <jnasby@pervasive.com>
parent: Sriram Dandapani <sdandapani@counterpane.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jim C. Nasby @ 2006-04-25 18:24 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: planner not using index for like operator
@ 2006-04-25 18:31 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 5+ messages in thread
From: Sriram Dandapani @ 2006-04-25 18:31 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: planner not using index for like operator
@ 2006-04-25 18:52 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 5+ messages in thread
From: Sriram Dandapani @ 2006-04-25 18:52 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: 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@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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-04-25 18:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-25 17:08 planner not using index for like operator Sriram Dandapani <sdandapani@counterpane.com>
2006-04-25 18:03 ` Dave Dutcher <dave@tridecap.com>
2006-04-25 18:24 ` Jim C. Nasby <jnasby@pervasive.com>
2006-04-25 18:31 Re: planner not using index for like operator Sriram Dandapani <sdandapani@counterpane.com>
2006-04-25 18:52 Re: planner not using index for like operator Sriram Dandapani <sdandapani@counterpane.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox