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 1F4725E40BB for ; Fri, 20 Aug 2004 20:59:04 -0300 (ADT) 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 39943-06 for ; Fri, 20 Aug 2004 23:59:00 +0000 (GMT) Received: from hm171.locaweb.com.br (hm171.locaweb.com.br [200.234.203.7]) by svr1.postgresql.org (Postfix) with SMTP id D08705E46D1 for ; Fri, 20 Aug 2004 20:58:56 -0300 (ADT) Received: (qmail 66197 invoked by uid 0); 20 Aug 2004 23:58:30 -0000 Received: from unknown (10.1.10.141) by hm171.locaweb.com.br with QMTP; 20 Aug 2004 23:58:30 -0000 Received: (qmail 11196 invoked from network); 20 Aug 2004 23:58:56 -0000 Received: from unknown (HELO xws08) (dmota@nexen.com.br@201.9.66.167) by hm141.locaweb.com.br with SMTP; 20 Aug 2004 23:58:56 -0000 Reply-To: From: "Danilo Mota" To: Subject: Query Performance Date: Fri, 20 Aug 2004 21:03:54 -0300 Organization: Nexen Tecnologia Message-ID: <000001c48712$599737a0$8afea8c0@nexen.lan> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C486F9.3449FFA0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_70_80, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200408/310 X-Sequence-Number: 7946 This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C486F9.3449FFA0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi all, the following query is working well without the AND on WHERE clause, so I need suggestions about how could I rewrite the query to get the same result with less cost of time and resources. I've already created indexes on all foreign key columns. Thanks in advance. Danilo Mota ======================================================================== ============ SELECT sn.notafiscalnumero, sn.notafiscalserie, CASE sn.notafiscaldata WHEN '00000000' THEN NULL ELSE to_date(sn.notafiscaldata,'YYYYMMDD') END, sn.modalidade, rcm.pkclientemarca, sn.notafiscalvalor/100, sn.entrada/100, sn.cliente FROM r_clientemarca AS rcm INNER JOIN r_cliente AS rc ON rc.pkcliente = rcm.fkcliente INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj = rc.cpfcnpj INNER JOIN sav_nota_lg AS sn ON sn.cliente = sc.codigo WHERE rcm.fkmarca = 1 AND sn.notafiscalnumero||sn.notafiscalserie||sn.cliente NOT IN ( SELECT numero||serie||codigo FROM r_contrato AS rcon WHERE savfonte = 'lg') ======================================================================== ============ TABLES ------------------------------------------------------------------------ ----------------------------------------------------- r_cliente: 75820 records r_clientemarca: 97719 records r_contrato: 782058 records sav_cliente_lg: 65671 records sav_nota_lg: 297329 rcords MY SERVER ------------------------------------------------------------------------ ----------------------------------------------------- Pentium 4 2.4 GHz 1 GB RAM 36 GB SCSI Postgresql 7.4.2 POSTGRESQL.CONF ------------------------------------------------------------------------ ----------------------------------------------------- shared_buffers = 7800 sort_mem = 4096 checkpoint_segments = 5 effective_cache_size = 12000 cpu_operator_cost = 0.0015 stats_start_collector = false QUERY PLAN ------------------------------------------------------------------------ ----------------------------------------------------- Hash Join (cost=27149.61..3090289650.24 rows=128765 width=4) Hash Cond: ("outer".cliente = "inner".codigo) -> Seq Scan on sav_nota_lg sn (cost=0.00..3090258517.99 rows=148665 width=8) Filter: (NOT (subplan)) SubPlan -> Seq Scan on r_contrato rcon (cost=0.00..20362.47 rows=282845 width=19) Filter: ((savfonte)::text = 'lg'::text) -> Hash (cost=26869.29..26869.29 rows=56880 width=4) -> Hash Join (cost=22473.95..26869.29 rows=56880 width=4) Hash Cond: ("outer".fkcliente = "inner".pkcliente) -> Index Scan using ix_r_clientemarca_fkmarca on r_clientemarca rcm (cost=0.00..2244.46 rows=65665 width=4) Index Cond: (fkmarca = 1) -> Hash (cost=22118.44..22118.44 rows=65672 width=8) -> Hash Join (cost=6613.22..22118.44 rows=65672 width=8) Hash Cond: (("outer".cpfcnpj)::text = ("inner".cpfcnpj)::text) -> Seq Scan on r_cliente rc (cost=0.00..12891.16 rows=75816 width=23) -> Hash (cost=6129.71..6129.71 rows=65671 width=23) -> Seq Scan on sav_cliente_lg sc (cost=0.00..6129.71 rows=65671 width=23) ------=_NextPart_000_0001_01C486F9.3449FFA0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi all,=

 <= /font>

 th= e following query is working well without the AND on WHERE clause, so I need suggestions about how could I rewrite the query to get the same result with less cost of time and resources.

 

I’ve already created indexes on all foreign key columns.

 

Thanks in advance.

 

Danilo <= span class=3DSpellE>Mota

 

=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D

    SELECT

        sn.notafiscalnumero,

        sn.notafiscalserie,

        CASE = sn.notafiscaldata WHEN '00000000' THEN NULL ELSE to_date(sn.notafiscaldata,'YYYYMMDD') END,<= /font>

        sn.mo= dalidade,

        rcm.p= kclientemarca,

        sn.no= tafiscalvalor/100,

        sn.entrada/100,

        sn.cliente

    FROM r_clientemarca AS= rcm

    INNER JOIN = r_cliente AS rc ON rc.pkcliente =3D rcm.fk= cliente

    INNER JOIN sav_cliente_lg AS sc ON sc.cpfcnpj =3D rc.cpfcnpj=

    INNER JOIN sav_nota_lg AS sn ON sn.cliente = =3D sc.codigo

    WHERE rcm.fkmarca =3D 1

      AND sn.notafiscalnumero||sn.notafiscalserie||sn.cliente N= OT IN ( SELECT numero||serie||codigo

        =             &nb= sp;            =             &nb= sp;            =                     =             &nb= sp;         FROM r_contrato AS rcon

        =             &nb= sp;            =             &nb= sp;             =                    =             &nb= sp;         WHERE savfonte =3D 'lg')

 <= /font>

=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D

 <= /font>

 

        =             &nb= sp;            =             &nb= sp;           TABLES

-------------------------= ---------------------------------------------------------------------------= -------------------------

r_cliente: 75820 records<= o:p>

r_clientemarca: 97719 rec= ords

r_contrato: 782058 record= s

sav_cliente_lg: 65671 rec= ords

sav_nota_lg: 297329 rcord= s

        =             &nb= sp;            =             &nb= sp;           MY SERVER

--------------------------------------------------------= ---------------------------------------------------------------------<= /o:p>

Pentium 4 2.4 GHz

1 GB RAM

36 GB SCSI

Postgresql 7.4.2

 <= /font>

        =             &nb= sp;            =             &nb= sp;           POSTGRESQL.CONF

--------------------------------------------------------= ---------------------------------------------------------------------<= /o:p>

shared_buffers = =3D 7800

sort_mem<= font size=3D2 face=3DArial> = =3D 4096

checkpoint_segments = =3D 5 

effective_cache_size = =3D 12000

cpu_operator_cost = =3D 0.0015

stats_start_collector = =3D false

 

        =             &nb= sp;            =             &nb= sp;           QUERY PLAN

--------------------------------------------------------= ---------------------------------------------------------------------<= /o:p>

 Hash Join  (co= st=3D27149.61..3090289650.24 rows=3D128765 width=3D4)

   Has= h Cond: ("outer".cliente= =3D "inner".codigo)=

   ->  Seq Scan on sav_nota_lg sn  (cost=3D0.00..3090258517.99 rows=3D148665 width=3D8)

         Filter: (NOT (subplan))

         SubPlan

        =    ->  Seq Scan on r_contrato rcon  (cost=3D0.00..20362.47 rows=3D282845 width=3D19)

        =          Filter: ((savfonte):= :text =3D 'lg'::text)

   ->  Hash  (cost=3D26869.29..26869.29 rows=3D= 56880 width=3D4)

         ->  Hash Join  (cost=3D22473.95..26869.= 29 rows=3D56880 width=3D4)

        =        Hash Cond: ("o= uter".fkcliente =3D "inner".pkcliente)

        =        ->  Index Scan using ix_r_clientemarca_fkmar= ca on r_clientemarca rcm  (cost=3D0.00..2244.46 rows=3D65665= width=3D4)

        =              Index Cond: (fkmarc= a =3D 1)

        =        ->  Hash  (cost=3D22118.44..22118.44 rows=3D= 65672 width=3D8)

               =       ->  Hash Join  (cost=3D6613.22..22118.44 rows=3D65672 width=3D8)

        =             &nb= sp;      Hash Cond: (("= outer".cpfcnpj)::text =3D ("inner".cpfc= npj)::text)

        =             &nb= sp;      ->  Seq Scan on r_cliente rc  (cost=3D0.00..12891.16 rows=3D75816 width=3D23)

        =             &nb= sp;      ->  Hash  (cost=3D6129.71..6129.71 rows=3D65= 671 width=3D23)

        =             &nb= sp;            ->  Seq Scan on sav_cliente_l= g sc  (cost=3D0.00..6129.71 rows= =3D65671 width=3D23)

 

 

------=_NextPart_000_0001_01C486F9.3449FFA0--