Re:

Bradley Baetz <bbaetz(at)student(dot)usyd(dot)edu(dot)au> writes:
> I'm having some problems with query optimisation, using postgresql 7.2.

It looks like on your hardware, seqscans are much cheaper relative to
indexscans than the optimizer is expecting. Note the ratios of cost
estimates to actual runtimes. You might care to experiment with the
optimizer parameters, such as random_page_cost, to see if you can get
closer to the actual behavior of your configuration.

> Also, in this case it would be better to use an inner join rather than a
> left join. Since there is a condition on cc.who which won't match NULL
> values from the cc table, an inner join should give the same results,
> shouldn't it?

It's not so much that you want an inner join as that you want to be able
to figure out that the cc.who=86 condition could be applied before
joining rather than after. I have not thought hard about how the
optimizer could determine whether this is a safe transformation. In
general it's obviously not safe when dealing with an outer join --- but
maybe in some cases we could allow it. Can anyone propose a rule?

regards, tom lane

PS: this discussion would be better suited for pgsql-hackers, I think.