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

Re: planner not using index for like operator

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Sriram Dandapani <sdandapani(at)counterpane(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:24:45
Message-ID: 20060425182444.GK97354@pervasive.com (view raw or flat)
Thread:
Lists: 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(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

pgsql-performance by date

Next:From: Sriram DandapaniDate: 2006-04-25 18:31:12
Subject: Re: planner not using index for like operator
Previous:From: Tom LaneDate: 2006-04-25 18:20:29
Subject: Re: Slow queries salad ;)

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