[PERFORM] Query Performance

[Danilo Mota]

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)