Search parameter optimization

From: Bruce De Vries <bpd(at)bpdconsulting(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Search parameter optimization
Date: 1999-10-21 17:42:00
Message-ID: 4.2.1.9.19991021091614.05cf3820@pacbell.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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';

even though

select * from ca where lower(city) like 'anaheim';

is faster than

select * from ca where lower(city) ~* '^anaheim';

I have issued a "vacuum analyze ca" command.

How to I get the fastest search results from complex expressions?

Is varchar or text better or is char better even though it takes up more
space? When searching a 5 digit zip code is char or int better?

-----
Bruce De Vries, Proprietor http://www.bpdconsulting.com
B. P. D. Consulting (714) 632-3841 Phone
2034 E. Lincoln Ave. PMB #344 (800) 828-9578 Toll Free
Anaheim, CA 92806 (603) 452-8504 FAX

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 1999-10-21 19:47:47 Re: [SQL] Search parameter optimization
Previous Message Pham, Thinh 1999-10-21 16:31:11 RE: [SQL] auto_increment?