Re: Query optimizing

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(at)archonet(dot)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
>