Query Performance

Поиск
Список
Период
Сортировка
От Danilo Mota
Тема Query Performance
Дата
Msg-id 000001c48712$599737a0$8afea8c0@nexen.lan
обсуждение исходный текст
Ответы Re: Query Performance  (Brad Bulger <brad@madfish.com>)
Список pgsql-performance

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)

 

 

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: using an index worst performances
Следующее
От: Brad Bulger
Дата:
Сообщение: Re: Query Performance