Re: Query optimizing - Mailing list pgsql-sql

Cheers for this Richard. The more I think about it, I believe the join is being made against ALL issues and followups
firstand then filtered by my where clause conditions afterwards. This would in incur a scan against all 15,000 issues
and95,000 followups. Set theory tells me that I should not use the entire issue table but rather the subset of interest
andthen join it to the followup table, instead of joining the two tables and then filtering the results. I was under
theimpression that the postgresql optimizer would have done this logically by itself. Could it have something to do
withthe fact that it is a subquery and thus the planner can not deduce filtering conditions from the outer query
againstit? My apologises if that made no sense.<br /><br />In summary, what im trying to understand is the following:
Willthere be a performance difference between filtering query sets first and then joining them together as opposed to
joiningfirst and then filtering? Does the opitmiser not choose the best course of action either way yielding the same
result?<br/><br />This might be a stupid question.<br />Sebastian<br /><br /><div class="gmail_quote">On Mon, Nov 10,
2008at 12:03 PM, Richard Huxton <span dir="ltr"><<a href="mailto:dev@archonet.com">dev@archonet.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex;
padding-left:1ex;"><div class="Ih2E3d">Richard Huxton wrote:<br /> > Do you have an index on (id,dt_modified) for
manage_followup?Can you<br /> >  provide an EXPLAIN ANALYSE for this?<br /><br /></div><div class="Ih2E3d">> Hi
Richard,<br/> ><br /> > Firstly, thank-you very much for your swift reply. To answer your<br /> > question,<br
/>> I had not been using an index on dt_modfied. I have added it now and<br /> > ran explain analyse on the
functionsnippet. I am almost too<br /> > embarrassed<br /> to show<br /> > the result....<br /> ><br /> >
QUERYPLAN<br /></div>[snip]<br /><div class="Ih2E3d">> Total runtime: 412464.804 ms!!!!<br /><br /></div>Something
wronghere. I've attacked a small script that generates 10,000<br /> issues and 10 follow-ups for each. It then pulls
offthe most recent<br /> follow-ups for all issues occurring on a given date.<br /><br /> The explain analyse should
showboth indexes being used and a runtime of<br /> a few milliseconds.<br /><font color="#888888"><br /> --<br />
 RichardHuxton<br />  Archonet Ltd<br /></font></blockquote></div><br />