X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2AEA73A3D1A for ; Mon, 17 Jan 2005 17:56:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32622-04 for ; Mon, 17 Jan 2005 17:56:07 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 33ADF3A2165 for ; Mon, 17 Jan 2005 17:56:07 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j0HHtnqJ003852; Mon, 17 Jan 2005 12:55:50 -0500 (EST) To: "Andrei Bintintan" Cc: pgsql-performance@postgresql.org Subject: Re: Optimizing this count query In-reply-to: <016801c4fcb5$cb36e8b0$0b00a8c0@forge> References: <016801c4fcb5$cb36e8b0$0b00a8c0@forge> Comments: In-reply-to "Andrei Bintintan" message dated "Mon, 17 Jan 2005 18:58:09 +0200" Date: Mon, 17 Jan 2005 12:55:49 -0500 Message-ID: <3851.1105984549@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/208 X-Sequence-Number: 9898 "Andrei Bintintan" 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