Re: query optimisation

Abdul-wahid Paterson wrote:
> Hi,
>
> I have a query of the form:
>
> select a.id, e.id from tablea a, tableb b, tablec c, tabled d, tablee e
> where a.fk=b.id and b.fk=c.id and c.fk=d.id and d.fk=e.id;
>
> In fact, my statement is a bit more complex as it is joined across 7
> tables, has an aggregate function and a CASE statement.
>
> My problem is that it executes to slow and I need to optimise it. Is
> there any documentation anywhere that can help me work out how to
> optimise the statement? My statement has a few other conditions that
> need to be met on the joining tables. Does changing the order of the
> conditional statement affect the way the statement is optimised?

Assuming indexes on the appropriate tables and a recent VACUUM
ANALYZE, I've found that using explicit joins can reduce
planning time significantly:

http://www.postgresql.org/docs/view.php?version=7.2&idoc=0&file=explicit-joins.html

Hope that helps,

Mike Mascari
mascarm@mascari.com