X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 750559DC861 for ; Thu, 5 Jan 2006 11:16:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02299-03 for ; Thu, 5 Jan 2006 11:16:57 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id B147D9DC812 for ; Thu, 5 Jan 2006 11:16:50 -0400 (AST) Received: from ar-sd.net (unknown [81.196.35.136]) by svr4.postgresql.org (Postfix) with ESMTP id AEFDA5AF07B for ; Thu, 5 Jan 2006 15:16:55 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by ar-sd.net (Postfix) with ESMTP id 60A6622F0D for ; Thu, 5 Jan 2006 17:16:51 +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 12744-05 for ; Thu, 5 Jan 2006 17:16:50 +0200 (EET) Received: from forge (unknown [192.168.0.11]) by ar-sd.net (Postfix) with SMTP id 2573825CE5 for ; Thu, 5 Jan 2006 17:16:50 +0200 (EET) Message-ID: <001d01c6120b$0d783600$0b00a8c0@forge> From: "Andy" To: Subject: Improving Inner Join Performance Date: Thu, 5 Jan 2006 17:16:47 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001A_01C6121B.CF02C500" 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, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200601/21 X-Sequence-Number: 16499 This is a multi-part message in MIME format. ------=_NextPart_000_001A_01C6121B.CF02C500 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi to all,=20 I have the following query: SELECT count(*) FROM orders o INNER JOIN report r ON r.id_order=3Do.id WHERE o.id_status>3 Explaing analyze: Aggregate (cost=3D8941.82..8941.82 rows=3D1 width=3D0) (actual = time=3D1003.297..1003.298 rows=3D1 loops=3D1) -> Hash Join (cost=3D3946.28..8881.72 rows=3D24041 width=3D0) = (actual time=3D211.985..951.545 rows=3D72121 loops=3D1) Hash Cond: ("outer".id_order =3D "inner".id) -> Seq Scan on report r (cost=3D0.00..2952.21 rows=3D72121 = width=3D4) (actual time=3D0.005..73.869 rows=3D72121 loops=3D1) -> Hash (cost=3D3787.57..3787.57 rows=3D24682 width=3D4) = (actual time=3D211.855..211.855 rows=3D0 loops=3D1) -> Seq Scan on orders o (cost=3D0.00..3787.57 = rows=3D24682 width=3D4) (actual time=3D0.047..147.170 rows=3D72121 = loops=3D1) Filter: (id_status > 3) Total runtime: 1003.671 ms I could use it in the following format, because I have to the moment = only the 4,6 values for the id_status. SELECT count(*) FROM orders o INNER JOIN report r ON r.id_order=3Do.id WHERE o.id_status IN (4,6) Explain analyze: Aggregate (cost=3D5430.04..5430.04 rows=3D1 width=3D0) (actual = time=3D1472.877..1472.877 rows=3D1 loops=3D1) -> Hash Join (cost=3D2108.22..5428.23 rows=3D720 width=3D0) (actual = time=3D342.080..1419.775 rows=3D72121 loops=3D1) Hash Cond: ("outer".id_order =3D "inner".id) -> Seq Scan on report r (cost=3D0.00..2952.21 rows=3D72121 = width=3D4) (actual time=3D0.036..106.217 rows=3D72121 loops=3D1) -> Hash (cost=3D2106.37..2106.37 rows=3D739 width=3D4) (actual = time=3D342.011..342.011 rows=3D0 loops=3D1) -> Index Scan using orders_id_status_idx, = orders_id_status_idx on orders o (cost=3D0.00..2106.37 rows=3D739 = width=3D4) (actual time=3D0.131..268.397 rows=3D72121 loops=3D1) Index Cond: ((id_status =3D 4) OR (id_status =3D 6)) Total runtime: 1474.356 ms How can I improve this query's performace?? The ideea is to count all = the values that I have in the database for the following conditions. If = the users puts in some other search fields on the where then the query = runs faster but in this format sometimes it takes a lot lot of = time(sometimes even 2,3 seconds).=20 Can this be tuned somehow??? Regards,=20 Andy. ------=_NextPart_000_001A_01C6121B.CF02C500 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi to all,
 
I have the following = query:
 
SELECT count(*) FROM orders=20 o
      INNER JOIN report r ON=20 r.id_order=3Do.id
      WHERE=20 o.id_status>3
 
Explaing analyze:
Aggregate  = (cost=3D8941.82..8941.82 rows=3D1=20 width=3D0) (actual time=3D1003.297..1003.298 rows=3D1 = loops=3D1)
  -> =20 Hash Join  (cost=3D3946.28..8881.72 rows=3D24041 width=3D0) (actual = time=3D211.985..951.545 rows=3D72121=20 loops=3D1)
        Hash Cond:=20 ("outer".id_order =3D = "inner".id)
       =20 ->  Seq Scan on report r  (cost=3D0.00..2952.21 = rows=3D72121 width=3D4)=20 (actual time=3D0.005..73.869 rows=3D72121=20 loops=3D1)
        ->  = Hash =20 (cost=3D3787.57..3787.57 rows=3D24682 width=3D4) (actual = time=3D211.855..211.855 rows=3D0=20 loops=3D1)
          = ;   =20 ->  Seq Scan on orders o  (cost=3D0.00..3787.57 = rows=3D24682 width=3D4)=20 (actual time=3D0.047..147.170 rows=3D72121=20 loops=3D1)
          = ;         =20 Filter: (id_status > 3)
Total runtime: 1003.671 ms
 
 
I could use it in the following format, = because I=20 have to the moment only the 4,6 values for the id_status.
 
SELECT count(*) FROM orders=20 o
      INNER JOIN report r ON=20 r.id_order=3Do.id
      WHERE = o.id_status IN=20 (4,6)
 
Explain analyze:
Aggregate =20 (cost=3D5430.04..5430.04 rows=3D1 width=3D0) (actual = time=3D1472.877..1472.877 rows=3D1=20 loops=3D1)
  ->  Hash Join  = (cost=3D2108.22..5428.23 rows=3D720=20 width=3D0) (actual time=3D342.080..1419.775 rows=3D72121=20 loops=3D1)
        Hash Cond:=20 ("outer".id_order =3D = "inner".id)
       =20 ->  Seq Scan on report r  (cost=3D0.00..2952.21 = rows=3D72121 width=3D4)=20 (actual time=3D0.036..106.217 rows=3D72121=20 loops=3D1)
        ->  = Hash =20 (cost=3D2106.37..2106.37 rows=3D739 width=3D4) (actual = time=3D342.011..342.011 rows=3D0=20 loops=3D1)
          = ;   =20 ->  Index Scan using orders_id_status_idx, orders_id_status_idx = on=20 orders o  (cost=3D0.00..2106.37 rows=3D739 width=3D4) (actual = time=3D0.131..268.397=20 rows=3D72121=20 loops=3D1)
          = ;         =20 Index Cond: ((id_status =3D 4) OR (id_status =3D 6))
Total runtime: = 1474.356=20 ms
How can I improve this query's = performace?? The=20 ideea is to count all the values that I have in the database for the = following=20 conditions. If the users puts in some other search fields on the where = then the=20 query runs faster but in this format sometimes it takes a lot lot of=20 time(sometimes even 2,3 seconds).
 
Can this be tuned = somehow???
 
Regards,
Andy.
 
 
------=_NextPart_000_001A_01C6121B.CF02C500--