| 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: | Whole Thread | Raw Message | 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? |