Re: Query Performance
От | Brad Bulger |
---|---|
Тема | Re: Query Performance |
Дата | |
Msg-id | 41269F38.5090305@madfish.com обсуждение исходный текст |
Ответ на | Query Performance ("Danilo Mota" <dmota@nexen.com.br>) |
Список | pgsql-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) > > > > >
В списке pgsql-performance по дате отправления: