Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA37237 for ; Thu, 21 Oct 1999 15:48:58 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id PAA17913; Thu, 21 Oct 1999 15:47:47 -0400 (EDT) To: Bruce De Vries cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Search parameter optimization In-reply-to: Your message of Thu, 21 Oct 1999 10:42:00 -0700 <4.2.1.9.19991021091614.05cf3820@pacbell.net> Date: Thu, 21 Oct 1999 15:47:47 -0400 Message-ID: <17911.940535267@sss.pgh.pa.us> From: Tom Lane Bruce De Vries writes: > I am using postgres to search databases with 1.75 million records. It's a > yellow pages database. I read through the mailing lists and learned how to > use the lower function to speed case insensitive searches, but am still > getting some results that don't make sence. For example: > select * from ca where lower(company) like 'baskin robbins' and lower(city) > ~* '^anaheim'; > executes 10 times faster than > select * from ca where lower(company) like 'baskin robbins' and lower(city) > like '^anaheim'; (I assume you didn't actually mean LIKE '^anaheim' there? ^ is a plain data character to LIKE, not a pattern anchor.) You certainly couldn't get a 10x speed differential just from the runtime of the matching operators. That has to be due to one query using a much more efficient query plan than the other. Rather than speculating about what the system is doing, how about you run an "explain" on each of these queries and show us the results? BTW, I trust you have indexes on lower(company) and lower(city)... regards, tom lane