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
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? |