Message-ID: <41269F38.5090305@madfish.com>
Date: Fri, 20 Aug 2004 18:02:48 -0700
From: Brad Bulger <brad@madfish.com>
Organization: The Madfish Group
User-Agent: Mozilla Thunderbird 0.7+ (Macintosh/20040715)
MIME-Version: 1.0
To: dmota@nexen.com.br
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Performance
References: <000001c48712$599737a0$8afea8c0@nexen.lan>
In-Reply-To: <000001c48712$599737a0$8afea8c0@nexen.lan>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Have you tried

AND (sn.notafiscalnumero, sn.notafiscalserie, sn.cliente) NOT IN (
   SELECT numero, serie, codigo FROM r_contrato WHERE savfonte = 'lg')

or

and not exists(select true from r_contrato where savfonte = 'lg' and numero = 
sn.notafiscalnumero and serie = sn.notafiscalserie and codigo = sn.cliente)


Danilo Mota wrote:

> 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)
> 
>  
> 
>  
>