Boa tarde,
Pessoal já esgotou aqui pelo meu lado, foi precisar da experiência de
vocês, minha aplicação faz uma consulta muito muito lenta que já está
gerando demandas de suporte.
Já criei e removi indices, fiz vacuum full, reindex e analyse e não surtiu
efeito. Está batendo um Total runtime: 353144.633 ms
No EXPLAIN ANALYSE vi que o maior custo está em um Index Scan, porém não
sei se é possível melhorar alguma coisa pelo lado do banco.
Por favor, alguma sugestão será muito bem vinda e apreciada. Mando o
resultado abaixo e o lik.
EXPLAIN ANALYZE SELECT '' as linha, '00:00' as nome,vehicle.vehiclecode
as vehiclecode, vehicle.vehiclecode as codonibus,vehicle.vehicleid as
vehicleVehicleid, public.vehicle.company_id as companyId, tb_company.name
as companyName from public.vehicle, tb_company where
public.vehicle.company_id in ( '1' , '3' ) and
tb_company.id = public.vehicle.company_id and public.vehicle.enabled
= true and not exists ( select * from vehiclebusserviceplanned vbp2
where vbp2.vehicle_vehicleid = vehicle.vehicleid and exists (select
vbp1.trip_program_id, max(vbp1.vehiclebusserviceplannedid) as
vehiclebusserviceplannedid from vehiclebusserviceplanned vbp1 where
vbp1.trip_program_id = vbp2.trip_program_id and ( CAST('2015-04-06' as
date) BETWEEN cast(vbp1.begintimestamp as date) and COALESCE(
cast(vbp1.endtimestamp as date), cast ('2015-04-06' as date))) group by
vbp1.trip_program_id having max(vbp1.vehiclebusserviceplannedid) =
vbp2.vehiclebusserviceplannedid)) ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Anti Join (cost=0.00..991302.60 rows=14 width=537) (actual
time=107324.312..353144.428 rows=7 loops=1)
-> Nested Loop (cost=0.00..109.11 rows=212 width=537) (actual
time=0.035..4.872 rows=213 loops=1)
Join Filter: (vehicle.company_id = tb_company.id)
-> Seq Scan on vehicle (cost=0.00..101.72 rows=212 width=21)
(actual time=0.020..3.363 rows=213 loops=1)
Filter: (enabled AND (company_id = ANY ('{1,3}'::bigint[])))
-> Materialize (cost=0.00..1.03 rows=2 width=524) (actual
time=0.001..0.002 rows=2 loops=213)
-> Seq Scan on tb_company (cost=0.00..1.02 rows=2
width=524) (actual time=0.004..0.006 rows=2 loops=1)
-> Index Scan using vehiclebusserviceplanned_vehicleid_idx on
vehiclebusserviceplanned vbp2 (cost=0.00..895426.36 rows=3814 width=8)
(actual time=1657.929..1657.929 rows=1 loops=213)
Index Cond: (vehicle_vehicleid = vehicle.vehicleid)
Filter: (SubPlan 1)
SubPlan 1
-> GroupAggregate (cost=0.00..117.33 rows=1 width=16) (actual
time=0.478..0.478 rows=0 loops=736406)
Filter: (max(vbp1.vehiclebusserviceplannedid) =
vbp2.vehiclebusserviceplannedid)
-> Index Scan using
vehiclebusserviceplanned_tripprogramid_idx on vehiclebusserviceplanned vbp1
(cost=0.00..117.25 rows=9 width=16) (actual time=0.387..0.476 rows=0
loops=736406)
Index Cond: (trip_program_id = vbp2.trip_program_id)
Filter: (('2015-04-06'::date >=
(begintimestamp)::date) AND ('2015-04-06'::date <=
COALESCE((endtimestamp)::date, '2015-04-06'::date)))
Total runtime: 353144.633 ms
http://explain.depesz.com/s/vilm
Como sempre obrigado pela ajuda.
--
José Ariel Ferreira Alves
[email protected]
[email protected]
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
