Re: [SQL] Search parameter optimization

Bruce  De Vries <bpd@bpdconsulting.com> 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