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)