Cheers for this Richard. The more I think about it, I believe the join is being made against ALL issues and followups first and then filtered by my where clause conditions afterwards. This would in incur a scan against all 15,000 issues and 95,000 followups. Set theory tells me that I should not use the entire issue table but rather the subset of interest and then join it to the followup table, instead of joining the two tables and then filtering the results. I was under the impression that the postgresql optimizer would have done this logically by itself. Could it have something to do with the fact that it is a subquery and thus the planner can not deduce filtering conditions from the outer query against it? My apologises if that made no sense.
In summary, what im trying to understand is the following: Will there be a performance difference between filtering query sets first and then joining them together as opposed to joining first and then filtering? Does the opitmiser not choose the best course of action either way yielding the same result?
This might be a stupid question.
Sebastian
On Mon, Nov 10, 2008 at 12:03 PM, Richard Huxton
<dev@archonet.com> wrote:
Richard Huxton wrote:
> Do you have an index on (id,dt_modified) for manage_followup? Can you
> provide an EXPLAIN ANALYSE for this?
> Hi Richard,
>
> Firstly, thank-you very much for your swift reply. To answer your
> question,
> I had not been using an index on dt_modfied. I have added it now and
> ran explain analyse on the function snippet. I am almost too
> embarrassed
to show
> the result....
>
> QUERY PLAN
[snip]
> Total runtime: 412464.804 ms!!!!
Something wrong here. I've attacked a small script that generates 10,000
issues and 10 follow-ups for each. It then pulls off the most recent
follow-ups for all issues occurring on a given date.
The explain analyse should show both indexes being used and a runtime of
a few milliseconds.
--
Richard Huxton
Archonet Ltd
