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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
