Received: from pippin.imagiware.com (IDENT:root@pippin.imagiware.com [205.254.196.9]) by hub.org (8.9.3/8.9.3) with ESMTP id OAA17933 for ; Thu, 21 Oct 1999 14:22:46 -0400 (EDT) (envelope-from bpd@bpdconsulting.com) Received: from office (unknown@ts003d43.lax-ca.concentric.net [206.173.208.151]) by pippin.imagiware.com (8.8.7/8.8.7) with ESMTP id NAA07084 for ; Thu, 21 Oct 1999 13:24:29 -0500 Message-Id: <4.2.1.9.19991021091614.05cf3820@pacbell.net> X-Sender: bpd@pacbell.net (Unverified) X-Mailer: QUALCOMM Windows Eudora Pro Version 4.2.1.9 (Beta) Date: Thu, 21 Oct 1999 10:42:00 -0700 To: pgsql-sql@postgresql.org From: Bruce De Vries Subject: Search parameter optimization Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed 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