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) |
- [PERFORM] Query performance Bill
- Re: [PERFORM] Query performance Richard Huxton
- Re: [PERFORM] Query performance Bill
- Re: [PERFORM] Query performance Richard Huxton
- Re: [PERFORM] Query performance Bill
- Re: [PERFORM] Query performance Richard Huxton
- Re: [PERFORM] Query performance Bruno Wolff III
- Re: [PERFORM] Query performance Bill
- Re: [PERFORM] Query performa... Rod Taylor
- Re: [PERFORM] Query performance Mischa Sandberg
- Re: [PERFORM] Query Performance Danilo Mota
- Re: [PERFORM] Query Performance Brad Bulger