query optimisation
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?
I am actually adopting someone else's work. In their code I found about
7-8 different SQL statements wrapped up in a couple of pages of
programming code that were used to get the desired result. The code
seemed to me long and convoluted and I was sure that it could be
whittled down to one SQL statement. However, it now take longer to run
as one SQL statement than it did when the external program was doing the
sorting and merging of data. Surely it should be the case, that in the
worst case, Postgres should still be more efficient then letting an
external program do the work? Are there occasions when it is better to
do the calculations outside of Postgres?
Thanks for any help.
Abdul-Wahid
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
Abdul-wahid Paterson <aw@lintrix.net> writes:
... Are there occasions when it is better to
do the calculations outside of Postgres?
There are, but without details it's impossible to opine whether yours is
one. A "simplified" version of the query is not enough detail. Give us
the exact query, the complete schemas for the tables involved, and the
information reported by EXPLAIN ANALYZE, and then we might be able to
offer useful help.
regards, tom lane
1. Have you checked whether all fields which you are joining are indexed.
2. Are you sure that your query is not have any subselects, if it have any
subselects convert into joins.
3.Check what is the size of tables. If tables size is very big do filter
condtions
4. Even if you feel that your query is still working slow, then denormalise
your database and avoid atleast some joins.
Regards,
Sreedhar
----- Original Message -----
From: "Abdul-wahid Paterson" <aw@lintrix.net>
To: <pgsql-general@postgresql.org>
Sent: Saturday, March 15, 2003 6:39 AM
Subject: [GENERAL] query optimisation
On Sunday, March 16, 2003, at 12:06 AM, shreedhar wrote:
[snip]
4. Even if you feel that your query is still working slow, then
denormalise
your database and avoid atleast some joins.[snip]
Don't denormalize your database. Your current situation, with an
external program, is better than that alternative.