Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 9CAC09FB1C2 for ; Thu, 11 Jan 2007 14:17:14 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 18761-04 for ; Thu, 11 Jan 2007 14:17:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from server.linux27.com (server.linux27.com [72.232.77.42]) by postgresql.org (Postfix) with ESMTP id EBD8C9F95E3 for ; Thu, 11 Jan 2007 14:17:05 -0400 (AST) Received: from [201.67.131.242] (helo=PC1) by server.linux27.com with esmtpa (Exim 4.63) (envelope-from ) id 1H54U7-0004Rr-Ro for pgsql-performance@postgresql.org; Thu, 11 Jan 2007 16:17:08 -0200 Reply-To: From: "Carlos H. Reimer" To: Subject: Improving SQL performance Date: Thu, 11 Jan 2007 16:17:06 -0200 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0000_01C7359B.EFE12060" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - server.linux27.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - opendb.com.br X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/176 X-Sequence-Number: 22777 This is a multi-part message in MIME format. ------=_NextPart_000_0000_01C7359B.EFE12060 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hi, I know that the problem with the following SQL is the "LOG.CODCEP = ENDE.CODCEP||CODLOG" condition, but what can I do to improve the performance? Is there a type of index that could help or is there another way to build this SQL? Thank you in advance! explain analyze SELECT ENDE.* , DEND.DESEND, DEND.USOEND, DEND.DUPEND, to_char('F') as NOVO, LOG.TIPLOG FROM TT_END ENDE LEFT OUTER JOIN TD_END DEND ON DEND.CODTAB = ENDE.TIPEND LEFT OUTER JOIN TT_LOG LOG ON LOG.CODCEP = ENDE.CODCEP||CODLOG WHERE ENDE.FILCLI = '001' AND ENDE.CODCLI = ' 19475'; QUERY PLAN ---------------------------------------------------------------------------- ------------------------------------------------------ Nested Loop Left Join (cost=0.00..25366.84 rows=1259 width=417) (actual time=1901.499..1901.529 rows=1 loops=1) Join Filter: (("inner".codcep)::text = (("outer".codcep)::text || ("outer".codlog)::text)) -> Nested Loop Left Join (cost=0.00..4.91 rows=1 width=412) (actual time=0.117..0.144 rows=1 loops=1) Join Filter: ("inner".codtab = "outer".tipend) -> Index Scan using pk_end on tt_end ende (cost=0.00..3.87 rows=1 width=388) (actual time=0.066..0.078 rows=1 loops=1) Index Cond: ((filcli = '001'::bpchar) AND (codcli = ' 19475'::bpchar)) -> Seq Scan on td_end dend (cost=0.00..1.02 rows=2 width=33) (actual time=0.012..0.018 rows=2 loops=1) -> Seq Scan on tt_log log (cost=0.00..12254.24 rows=582424 width=17) (actual time=0.013..582.521 rows=582424 loops=1) Total runtime: 1901.769 ms (9 rows) \d tt_log Table "TOTALL.tt_log" Column | Type | Modifiers --------+------------------------+----------- codbai | numeric(5,0) | not null nomlog | character varying(55) | not null codcep | character(8) | not null \d tt_end Table "TOTALL.tt_end" Column | Type | Modifiers --------+-----------------------+----------------------------------------- ... ... ... codlog | character(3) | ... ... ... codcep | character(5) | ... ... Reimer ------=_NextPart_000_0000_01C7359B.EFE12060 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I know=20 that the problem with the following SQL is the "LOG.CODCEP =3D=20 ENDE.CODCEP||CODLOG" condition, but what can I
do to improve the=20 performance?
 
Is there a type of index that could = help or is=20 there another way to build this SQL?
 
Thank you in advance!
 
explain analyze
SELECT ENDE.* , = DEND.DESEND,=20 DEND.USOEND,=20 DEND.DUPEND,
         &nb= sp;    =20 to_char('F') as=20 NOVO,
          &nbs= p;   =20 LOG.TIPLOG
          = FROM TT_END=20 ENDE LEFT OUTER JOIN TD_END DEND ON DEND.CODTAB =3D=20 ENDE.TIPEND
         &nbs= p;            = ;    =20 LEFT OUTER JOIN TT_LOG LOG ON LOG.CODCEP =3D=20 ENDE.CODCEP||CODLOG
         = WHERE=20 ENDE.FILCLI =3D=20 '001'
           = AND=20 ENDE.CODCLI =3D ' 19475';
 

          &nbs= p;            = ;            =             &= nbsp;           =20 QUERY=20 PLAN           &nb= sp;           &nbs= p;            = ;            =       =20
---------------------------------------------------------------------= -------------------------------------------------------------
 Ne= sted=20 Loop Left Join  (cost=3D0.00..25366.84 rows=3D1259 width=3D417) = (actual=20 time=3D1901.499..1901.529 rows=3D1 loops=3D1)
   Join = Filter:=20 (("inner".codcep)::text =3D (("outer".codcep)::text ||=20 ("outer".codlog)::text))
   ->  Nested Loop Left = Join =20 (cost=3D0.00..4.91 rows=3D1 width=3D412) (actual time=3D0.117..0.144 = rows=3D1=20 loops=3D1)
         Join = Filter:=20 ("inner".codtab =3D=20 "outer".tipend)
         = -> =20 Index Scan using pk_end on tt_end ende  (cost=3D0.00..3.87 rows=3D1 = width=3D388)=20 (actual time=3D0.066..0.078 rows=3D1=20 loops=3D1)
          = ;    =20 Index Cond: ((filcli =3D '001'::bpchar) AND (codcli =3D '=20 19475'::bpchar))
         = -> =20 Seq Scan on td_end dend  (cost=3D0.00..1.02 rows=3D2 width=3D33) = (actual=20 time=3D0.012..0.018 rows=3D2 loops=3D1)
   ->  Seq = Scan on tt_log=20 log  (cost=3D0.00..12254.24 rows=3D582424 width=3D17) (actual = time=3D0.013..582.521=20 rows=3D582424 loops=3D1)
 Total runtime: 1901.769 ms
(9 = rows)
 
\d=20 tt_log
          &nb= sp;=20 Table "TOTALL.tt_log"
 Column=20 |         =20 Type          |=20 Modifiers
--------+------------------------+-----------
 codba= i |=20 numeric(5,0)           = | not=20 null
 nomlog | character varying(55)  | not = null
 codcep |=20 character(8)           = | not=20 null
 
\d=20 tt_end
          &nb= sp;           &nbs= p;  =20 Table "TOTALL.tt_end"
 Column=20 |        =20 Type         =20 |            =    =20 Modifiers
--------+-----------------------+---------------------------= --------------
...
...
...
 codlog=20 | character(3)         =20 |
...
...
...
 codcep |=20 character(5)         =20 |
...
...

Reimer

 
------=_NextPart_000_0000_01C7359B.EFE12060--