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 A1AC33A3CE3 for ; Mon, 17 Jan 2005 16:58:48 +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 25500-08 for ; Mon, 17 Jan 2005 16:58:37 +0000 (GMT) Received: from ar-sd.net (unknown [82.77.155.72]) by svr1.postgresql.org (Postfix) with ESMTP id 91D823A3D58 for ; Mon, 17 Jan 2005 16:58:35 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id E9C6C212E2 for ; Mon, 17 Jan 2005 18:54:27 +0200 (EET) Received: from ar-sd.net ([127.0.0.1]) by localhost (linz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 02866-08 for ; Mon, 17 Jan 2005 18:54:27 +0200 (EET) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id B64951A9E4 for ; Mon, 17 Jan 2005 18:54:26 +0200 (EET) Message-ID: <016801c4fcb5$cb36e8b0$0b00a8c0@forge> From: "Andrei Bintintan" To: Subject: Optimizing this count query Date: Mon, 17 Jan 2005 18:58:09 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0160_01C4FCC6.7CA02250" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at ar-sd.net X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200501/204 X-Sequence-Number: 9894 This is a multi-part message in MIME format. ------=_NextPart_000_0160_01C4FCC6.7CA02250 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi to all,=20 I have a query which counts how many elements I have in the database. SELECT count(o.id) FROM orders o INNER JOIN report r ON o.id=3Dr.id_order INNER JOIN status s ON o.id_status=3Ds.id INNER JOIN contact c ON o.id_ag=3Dc.id INNER JOIN endkunde e ON o.id_endkunde=3De.id INNER JOIN zufriden z ON r.id_zufriden=3Dz.id INNER JOIN plannung v ON v.id=3Do.id_plannung INNER JOIN mpsworker w ON v.id_worker=3Dw.id INNER JOIN person p ON p.id =3D w.id_person WHERE o.id_status>3=20 In the tables are not quite so many rows (~ 100000). I keep the joins because in the where clause there can be also other = search elemens which are searched in the other tables.=20 Now the id_status from the orders table (>3) can be 4 or 6. The = id_status=3D6 has the most bigger percentage (4 =3D 10%, 6 =3D 70% and = the rest are other statuses < 4). I think this is why the planner uses=20 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. Best regards,=20 Andy. The explain: Aggregate (cost=3D37931.33..37931.33 rows=3D1 width=3D4) -> Hash Join (cost=3D27277.86..37828.45 rows=3D41154 width=3D4) Hash Cond: ("outer".id_person =3D "inner".id) -> Hash Join (cost=3D27269.79..37100.18 rows=3D41153 = width=3D8) Hash Cond: ("outer".id_worker =3D "inner".id) -> Hash Join (cost=3D27268.28..36378.50 rows=3D41152 = width=3D8) Hash Cond: ("outer".id_endkunde =3D "inner".id) -> Hash Join (cost=3D25759.54..33326.98 = rows=3D41151 width=3D12) Hash Cond: ("outer".id_ag =3D "inner".id) -> Hash Join (cost=3D25587.07..32331.51 = rows=3D41150 width=3D16) Hash Cond: ("outer".id_status =3D = "inner".id) -> Hash Join = (cost=3D25586.00..31713.18 rows=3D41150 width=3D20) Hash Cond: ("outer".id_zufriden = =3D "inner".id) -> Hash Join = (cost=3D25584.85..31094.78 rows=3D41150 width=3D24) Hash Cond: = ("outer".id_plannung =3D "inner".id) -> Hash Join = (cost=3D24135.60..27869.53 rows=3D41149 width=3D24) Hash Cond: ("outer".id = =3D "inner".id_order) -> Seq Scan on orders = o (cost=3D0.00..2058.54 rows=3D42527 width=3D20) Filter: = (id_status > 3) -> Hash = (cost=3D23860.48..23860.48 rows=3D42848 width=3D8) -> Seq Scan on = report r (cost=3D0.00..23860.48 rows=3D42848 width=3D8) -> Hash = (cost=3D1050.80..1050.80 rows=3D62180 width=3D8) -> Seq Scan on = plannung v (cost=3D0.00..1050.80 rows=3D62180 width=3D8) -> Hash (cost=3D1.12..1.12 = rows=3D12 width=3D4) -> Seq Scan on zufriden z = (cost=3D0.00..1.12 rows=3D12 width=3D4) -> Hash (cost=3D1.06..1.06 rows=3D6 = width=3D4) -> Seq Scan on status s = (cost=3D0.00..1.06 rows=3D6 width=3D4) -> Hash (cost=3D161.57..161.57 rows=3D4357 = width=3D4) -> Seq Scan on contact c = (cost=3D0.00..161.57 rows=3D4357 width=3D4) -> Hash (cost=3D1245.99..1245.99 rows=3D44299 = width=3D4) -> Seq Scan on endkunde e = (cost=3D0.00..1245.99 rows=3D44299 width=3D4) -> Hash (cost=3D1.41..1.41 rows=3D41 width=3D8) -> Seq Scan on mpsworker w (cost=3D0.00..1.41 = rows=3D41 width=3D8) -> Hash (cost=3D7.66..7.66 rows=3D166 width=3D4) -> Seq Scan on person p (cost=3D0.00..7.66 rows=3D166 = width=3D4) ------=_NextPart_000_0160_01C4FCC6.7CA02250 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi to=20 all,
 
I have a query which counts how many elements I have = in the=20 database.
 
SELECT count(o.id) FROM orders=20 o
      INNER JOIN report r ON=20 o.id=3Dr.id_order
      INNER JOIN = status s ON=20 o.id_status=3Ds.id
      INNER JOIN = contact c ON=20 o.id_ag=3Dc.id
      INNER JOIN = endkunde e ON=20 o.id_endkunde=3De.id
      INNER JOIN = zufriden z=20 ON r.id_zufriden=3Dz.id
      INNER = JOIN plannung=20 v ON v.id=3Do.id_plannung
      INNER = JOIN=20 mpsworker w ON = v.id_worker=3Dw.id
      INNER=20 JOIN person p ON p.id =3D = w.id_person
      WHERE=20 o.id_status>3
 
In the tables are not quite so many = rows (~=20 100000).
 
I keep the joins because in the where = clause there=20 can be also other search elemens which are searched in the other tables. =
Now the id_status from the orders table = (>3) can=20 be 4 or 6. The id_status=3D6 has the most bigger percentage (4 =3D = 10%, 6 =3D 70%=20 and the rest are other statuses < 4). I think this is why the planner = uses=20
 
I'm asking how can I improve the = execution time of=20 this query, because these tables are always increasing. And this count = sometimes=20 takes more than 10 secs and I need to run this count very = offen.
 
Best regards,
Andy.
 
 
The explain:
Aggregate  = (cost=3D37931.33..37931.33 rows=3D1=20 width=3D4)
  ->  Hash Join  = (cost=3D27277.86..37828.45=20 rows=3D41154 width=3D4)
        = Hash Cond:=20 ("outer".id_person =3D = "inner".id)
       =20 ->  Hash Join  (cost=3D27269.79..37100.18 rows=3D41153=20 width=3D8)
          = ;   =20 Hash Cond: ("outer".id_worker =3D=20 "inner".id)
         &nbs= p;   =20 ->  Hash Join  (cost=3D27268.28..36378.50 rows=3D41152=20 width=3D8)
          = ;         =20 Hash Cond: ("outer".id_endkunde =3D=20 "inner".id)
         &nbs= p;         =20 ->  Hash Join  (cost=3D25759.54..33326.98 rows=3D41151=20 width=3D12)
         &nbs= p;            = ;   =20 Hash Cond: ("outer".id_ag =3D=20 "inner".id)
         &nbs= p;            = ;   =20 ->  Hash Join  (cost=3D25587.07..32331.51 rows=3D41150=20 width=3D16)
         &nbs= p;            = ;         =20 Hash Cond: ("outer".id_status =3D=20 "inner".id)
         &nbs= p;            = ;         =20 ->  Hash Join  (cost=3D25586.00..31713.18 rows=3D41150=20 width=3D20)
         &nbs= p;            = ;            =    =20 Hash Cond: ("outer".id_zufriden =3D=20 "inner".id)
         &nbs= p;            = ;            =    =20 ->  Hash Join  (cost=3D25584.85..31094.78 rows=3D41150=20 width=3D24)
         &nbs= p;            = ;            =          =20 Hash Cond: ("outer".id_plannung =3D=20 "inner".id)
         &nbs= p;            = ;            =          =20 ->  Hash Join  (cost=3D24135.60..27869.53 rows=3D41149=20 width=3D24)
         &nbs= p;            = ;            =             &= nbsp;  =20 Hash Cond: ("outer".id =3D=20 "inner".id_order)
        &nbs= p;            = ;            =             &= nbsp;   =20 ->  Seq Scan on orders o  (cost=3D0.00..2058.54 = rows=3D42527=20 width=3D20)
         &nbs= p;            = ;            =             &= nbsp;        =20 Filter: (id_status >=20 3)
           &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p; =20 ->  Hash  (cost=3D23860.48..23860.48 rows=3D42848=20 width=3D8)
          = ;            =             &= nbsp;           &n= bsp;        =20 ->  Seq Scan on report r  (cost=3D0.00..23860.48 = rows=3D42848=20 width=3D8)
          = ;            =             &= nbsp;        =20 ->  Hash  (cost=3D1050.80..1050.80 rows=3D62180=20 width=3D8)
          = ;            =             &= nbsp;           &n= bsp;  =20 ->  Seq Scan on plannung v  (cost=3D0.00..1050.80 = rows=3D62180=20 width=3D8)
          = ;            =             &= nbsp;  =20 ->  Hash  (cost=3D1.12..1.12 rows=3D12=20 width=3D4)
          = ;            =             &= nbsp;        =20 ->  Seq Scan on zufriden z  (cost=3D0.00..1.12 rows=3D12=20 width=3D4)
          = ;            =          =20 ->  Hash  (cost=3D1.06..1.06 rows=3D6=20 width=3D4)
          = ;            =             &= nbsp;  =20 ->  Seq Scan on status s  (cost=3D0.00..1.06 rows=3D6=20 width=3D4)
          = ;            =    =20 ->  Hash  (cost=3D161.57..161.57 rows=3D4357=20 width=3D4)
          = ;            =          =20 ->  Seq Scan on contact c  (cost=3D0.00..161.57 rows=3D4357 = width=3D4)
          = ;         =20 ->  Hash  (cost=3D1245.99..1245.99 rows=3D44299=20 width=3D4)
          = ;            =    =20 ->  Seq Scan on endkunde e  (cost=3D0.00..1245.99 = rows=3D44299=20 width=3D4)
          = ;   =20 ->  Hash  (cost=3D1.41..1.41 rows=3D41=20 width=3D8)
          = ;         =20 ->  Seq Scan on mpsworker w  (cost=3D0.00..1.41 rows=3D41=20 width=3D8)
        ->  = Hash =20 (cost=3D7.66..7.66 rows=3D166=20 width=3D4)
          = ;   =20 ->  Seq Scan on person p  (cost=3D0.00..7.66 rows=3D166=20 width=3D4)
------=_NextPart_000_0160_01C4FCC6.7CA02250--