Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 9FA38476476
	for <pgsql-sql@postgresql.org>; Mon, 13 Jan 2003 16:56:44 -0500 (EST)
Received: from web14103.mail.yahoo.com (web14103.mail.yahoo.com
	[216.136.172.133])
	by postgresql.org (Postfix) with SMTP id EF0CE4770F9
	for <pgsql-sql@postgresql.org>; Mon, 13 Jan 2003 16:56:41 -0500 (EST)
Message-ID: <20030113215642.24981.qmail@web14103.mail.yahoo.com>
Received: from [64.192.116.161] by web14103.mail.yahoo.com via HTTP;
	Mon, 13 Jan 2003 13:56:42 PST
Date: Mon, 13 Jan 2003 13:56:42 -0800 (PST)
From: Christopher Smith <christopherl_smith@yahoo.com>
Subject: Re: query speed joining tables
To: josh@agliodbs.com, pgsql-sql@postgresql.org
In-Reply-To: <200301131347.49013.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1924479903-1042495002=:24868"
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/189
X-Sequence-Number: 11054

--0-1924479903-1042495002=:24868
Content-Type: text/plain; charset=us-ascii


I understand cachable functions but your proposed application is a little unclear.
is it possible to see an example?
thanks in advance.
 Josh Berkus <josh@agliodbs.com> wrote:
Chris,

Here are probably your two main query problems:

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

It is impossible for Postgres to use an index for the above as it is written. 
I would suggest one of two improvements;
1) Modify the schema so that multiple ethnicity details are kept in a 
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 where 
zips_max.origin='90210' )
> order by user_login.last_login desc;

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

-- 
-Josh Berkus
Aglio Database Solutions
San Francisco


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly


---------------------------------
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now
--0-1924479903-1042495002=:24868
Content-Type: text/html; charset=us-ascii

<P>I&nbsp;understand cachable functions but your proposed application is a little unclear.
<P>is it possible to see an example?
<P>thanks in advance.
<P>&nbsp;<B><I>Josh Berkus &lt;josh@agliodbs.com&gt;</I></B> wrote:
<BLOCKQUOTE style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid"><BR>Chris,<BR><BR>Here are probably your two main query problems:<BR><BR>&gt; strpos(user_match_details.ethnicity,'Asian') !=0 AND<BR><BR>It is impossible for Postgres to use an index for the above as it is written. <BR>I would suggest one of two improvements;<BR>1) Modify the schema so that multiple ethnicity details are kept in a <BR>sub-table rather than a free-form text field you have to search, or:<BR>2) Create a cachable function for "contains_asian" and index on that.<BR><BR>&gt; user_details_p.zipcode in (select zips_max.destination from zips_max where <BR>zips_max.origin='90210' )<BR>&gt; order by user_login.last_login desc;<BR><BR>Use a "WHERE EXISTS" clause instead of "IN". <BR><BR>-- <BR>-Josh Berkus<BR>Aglio Database Solutions<BR>San Francisco<BR><BR><BR>---------------------------(end of broadcast)---------------------------<BR>TIP 3: if posting/reading through Usenet, please
  send an appropriate<BR>subscribe-nomail command to majordomo@postgresql.org so that your<BR>message can get through to the mailing list cleanly</BLOCKQUOTE><p><br><hr size=1>Do you Yahoo!?<br>
<a href="http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com">Yahoo! Mail Plus</a> - Powerful. Affordable. <a href="http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.com">Sign up now</a>
--0-1924479903-1042495002=:24868--