Re: Query Performance

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