On 28-11-2014 16:13, Ariel Alves wrote: > Boa tarde prezados, > > > Venho com mais um pedido de ajuda a vocês, estou homologando uma aplicação > e logo no inicio já me deparei com uma consulta muito lenta, peguei o > resultado explain analyze, vi que a lentidão estava concentrada > exclusivamente em um "Index Scan Backward". Apesar de está com o resultado > do explain não sei muito como resolver este problema. > > Como a base é nova (restore recente) não arrisquei nem em fazer um reindex. > > O explain está na http://explain.depesz.com/s/Jiu > > > Se alguém tiver alguma sugestão de configuração para transações deste tipo > agradeço muito. > > > PLAN > > > -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Sort (cost=191.32..191.32 rows=1 width=61) (actual > time=555232.289..555232.289 rows=0 loops=1) > Sort Key: tp.departure_time > Sort Method: quicksort Memory: 25kB > -> Nested Loop (cost=8.02..191.31 rows=1 width=61) (actual > time=555232.259..555232.259 rows=0 loops=1) > -> Nested Loop (cost=8.02..186.13 rows=1 width=61) (actual > time=555232.258..555232.258 rows=0 loops=1) > -> Nested Loop (cost=8.02..183.65 rows=1 width=69) (actual > time=555232.257..555232.257 rows=0 loops=1) > -> Bitmap Heap Scan on tb_trip_program tp > (cost=8.02..181.17 rows=1 width=61) (actual time=555232.256..555232.256 > rows=0 loops=1) > Recheck Cond: ((program_id = 7258::bigint) AND > (control_point_id = 13892::bigint)) > Filter: (SubPlan 3) > -> BitmapAnd (cost=8.02..8.02 rows=1 width=0) > (actual time=0.129..0.129 rows=0 loops=1) > -> Bitmap Index Scan on > tripprogram_programid_idx (cost=0.00..2.04 rows=89 width=0) (actual > time=0.039..0.039 rows=74 loops=1) > Index Cond: (program_id = > 7258::bigint) > -> Bitmap Index Scan on > tripprogram_controlpointid_idx (cost=0.00..5.73 rows=434 width=0) (actual > time=0.087..0.087 rows=437 loops=1) > Index Cond: (control_point_id = > 13892::bigint) > SubPlan 3 > -> Subquery Scan on x (cost=0.00..172.03 > rows=1 width=0) (actual time=15006.263..15006.263 rows=0 loops=37) > Filter: (x.vehicle_vehicleid = > 3883::bigint) > -> Limit (cost=0.00..172.02 rows=1 > width=16) (actual time=15006.260..15006.260 rows=0 loops=37) > -> Index Scan Backward using > vehiclebusserviceplanned_pkey on vehiclebusserviceplanned > (cost=0.00..1971027.10 rows=11458 width=16) (actual > time=15006.257..15006.257 rows=0 loops=37) > Filter: ((trip_program_id = > tp.id) AND ((((('2014-11-28'::date)::text || ' '::text) || > (tp.departure_time)::text))::timestamp without time zone >= begintimestamp) > AND ((((('2014-11-28'::date)::text || ' '::text) || > (tp.departure_time)::text))::timestamp without time zone <= > COALESCE(endtimestamp, (((('2014-11-28'::date)::text || ' '::text) || > (tp.departure_time)::text))::timestamp without time zone))) > -> Index Scan using controlpoint_pkey on controlpoint > (cost=0.00..2.47 rows=1 width=16) (never executed) > Index Cond: (controlpointid = 13892::bigint) > -> Index Scan using pattern_pkey on pattern > (cost=0.00..2.47 rows=1 width=8) (never executed) > Index Cond: (patternid = > controlpoint.pattern_patternid) > Filter: (busservice_busserviceid = 405::bigint) > -> Index Scan using tb_program_pkey on tb_program > (cost=0.00..2.48 rows=1 width=8) (never executed) > Index Cond: (id = 7258::bigint) > Filter: (('2014-11-28'::date >= (initial_term)::date) AND > ('2014-11-28'::date <= (COALESCE(final_term, '2014-11-28 > 14:18:13'::timestamp without time zone))::date)) > SubPlan 1 > -> Index Scan using executiontrip_beginningexecution_idx on > tb_execution_trip et (cost=0.00..2.69 rows=1 width=0) (never executed) > Index Cond: ((beginning_execution >= '2014-11-28 > 00:00:00'::timestamp without time zone) AND (beginning_execution < > '2014-11-29 00:00:00'::timestamp without time zone)) > Filter: (((status_journey)::text = ANY > ('{COMPLETA,INICIADA}'::text[])) AND (trip_program_id = tp.id)) > SubPlan 2 > -> Index Scan using executiontrip_beginningexecution_idx on > tb_execution_trip et (cost=0.00..2.69 rows=1 width=8) (never executed) > Index Cond: ((beginning_execution >= '2014-11-28 > 00:00:00'::timestamp without time zone) AND (beginning_execution < > '2014-11-29 00:00:00'::timestamp without time zone)) > Filter: ((status_journey)::text = ANY > ('{COMPLETA,INICIADA}'::text[])) > Total runtime: 555232.626 ms > (37 rows) >
As estatísticas estão atualizadas? Tentou executar antes um ANALYZE nas tabelas envolvidas para garantir que não são estatísticas obsoletas ou inexistentes? -- Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
signature.asc
Description: OpenPGP digital signature
_______________________________________________ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
