pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrei Bintintan <klodoma@ar-sd.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizing this count query
Date: Mon, 17 Jan 2005 12:55:49 -0500
Message-ID: <3851.1105984549@sss.pgh.pa.us> (raw)
In-Reply-To: <016801c4fcb5$cb36e8b0$0b00a8c0@forge>
References: <016801c4fcb5$cb36e8b0$0b00a8c0@forge>

"Andrei Bintintan" <klodoma@ar-sd.net> writes:
> SELECT count(o.id) FROM orders o
>       INNER JOIN report r ON o.id=r.id_order
>       INNER JOIN status s ON o.id_status=s.id
>       INNER JOIN contact c ON o.id_ag=c.id
>       INNER JOIN endkunde e ON o.id_endkunde=e.id
>       INNER JOIN zufriden z ON r.id_zufriden=z.id
>       INNER JOIN plannung v ON v.id=o.id_plannung
>       INNER JOIN mpsworker w ON v.id_worker=w.id
>       INNER JOIN person p ON p.id = w.id_person
>       WHERE o.id_status>3

> I'm asking how can I improve the execution time of this query, because =
> these tables are always increasing. And this count sometimes takes more =
> than 10 secs and I need to run this count very offen.

Unless you've increased the default value of join_collapse_limit, this
construction will be forcing the join order; see
http://www.postgresql.org/docs/7.4/static/explicit-joins.html

I'm not sure if you can improve the join order at all --- since you only
showed EXPLAIN and not EXPLAIN ANALYZE, it's hard to be sure whether any
of the steps are producing large intermediate results.  But it's
something to look into.

You should also ask yourself if you need to be joining so many tables at
all.  The planner seems to think that only the o/r join is really going
to affect the result row count.  I can't tell if it's right or not, but
if this is a star schema and the other seven tables are just detail
tables, you don't need them in order to obtain a count.

			regards, tom lane



view thread (3+ messages)  latest in thread

Message-ID: <3851.1105984549@sss.pgh.pa.us>
Permalink:  ../3851.1105984549@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3851.1105984549@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, klodoma@ar-sd.net
  Subject: Re: Optimizing this count query
  In-Reply-To: <3851.1105984549@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox