Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Christopher Smith <christopherl_smith@yahoo.com>, pgsql-sql@postgresql.org
Subject: Re: query speed joining tables
Date: Mon, 13 Jan 2003 13:47:49 -0800
References: <20030113212052.60493.qmail@web14107.mail.yahoo.com>
In-Reply-To: <20030113212052.60493.qmail@web14107.mail.yahoo.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200301131347.49013.josh@agliodbs.com>


Chris,

Here are probably your two main query problems:

> strpos(user_match_details.ethnicity,'Asian') !=3D0 AND

It is impossible for Postgres to use an index for the above as it is writte=
n.=20=20
I would suggest one of two improvements;
1) Modify the schema so that multiple ethnicity details are kept in a=20
sub-table rather than a free-form text field you have to search, or:
2) Create a cachable function for "contains_asian" and index on that.

> user_details_p.zipcode in (select zips_max.destination from zips_max wher=
e=20
zips_max.origin=3D'90210' )
> order by user_login.last_login desc;

Use a "WHERE EXISTS" clause instead of "IN".=20=20

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco