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) -- José Ariel Ferreira Alves arielalves...@gmail.com ariel.al...@msn.com
_______________________________________________ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
