Re: query speed joining tables

<p>I understand cachable functions but your proposed application is a little unclear. <p>is it possible to see an
example?<p>thanks in advance. <p> <b><i>Josh Berkus <josh@agliodbs.com></i></b> wrote: <blockquote
style="PADDING-LEFT:5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid"><br />Chris,<br /><br />Here are probably
yourtwo main query problems:<br /><br />> strpos(user_match_details.ethnicity,'Asian') !=0 AND<br /><br />It is
impossiblefor 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-formtext field you have to search, or:<br />2) Create a cachable function for "contains_asian" and index on
that.<br/><br />> user_details_p.zipcode in (select zips_max.destination from zips_max where <br
/>zips_max.origin='90210')<br />> order by user_login.last_login desc;<br /><br />Use a "WHERE EXISTS" clause
insteadof "IN". <br /><br />-- <br />-Josh Berkus<br />Aglio Database Solutions<br />San Francisco<br /><br /><br
/>---------------------------(endof 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
canget 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">Signup now</a>