Search parameter optimization
От | Bruce De Vries |
---|---|
Тема | Search parameter optimization |
Дата | |
Msg-id | 4.2.1.9.19991021091614.05cf3820@pacbell.net обсуждение исходный текст |
Ответы |
Re: [SQL] Search parameter optimization
|
Список | 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
В списке pgsql-sql по дате отправления: