[pgbr-geral] Consulta Muito lenta
Boa tarde amigos, tenho uma consulta que faz referencia a duas tabelas, uma onde tem os dados que realmente quero consultar, e outra onde testo se ouve uma devolucao de tal produto... se ouve, ele resta da coluna quantidade quandos foram devolvidos. o problema está que eu recem estou aprendendo, e esta consulta em alguns clientes com um volumem de dados consideravel, esta demorando muito... algum amigo poderia me dar uma mao? segue o explain da consulta.. e mais a baixo a consulta propiamente. Nested Loop (cost=1828.15..1841.35 rows=330 width=319) -> Sort (cost=836.84..837.67 rows=330 width=102) Sort Key: public.movis.c_cpd -> HashAggregate (cost=759.51..823.04 rows=330 width=102) -> Index Scan using ind_emismovis on movis (cost= 0.00..619.35 rows=3298 width=102) Index Cond: ((d_emis >= '2007-08-01'::date) AND (d_emis <= '2007-08-21'::date)) Filter: ((c_tipo = 'V'::bpchar) AND ((n_cant - devolvidos(c_cpd, c_nota)) > 0::numeric)) -> Materialize (cost=991.31..991.32 rows=1 width=32) -> Aggregate (cost=991.27..991.30 rows=1 width=48) -> Seq Scan on movis (cost=0.00..960.36 rows=6181 width=48) Filter: (((d_emis)::text >= '20070801'::text) AND (d_emis <= '2007-08-21'::date) AND (c_tipo = 'V'::bpchar)) SELECT lucrorentabilidad.c_cpd, lucrorentabilidad.c_descr, lucrorentabilidad.cant, lucrorentabilidad.costounit, lucrorentabilidad.ventaunit, lucrorentabilidad.totcosto, lucrorentabilidad.totventa, lucrorentabilidad.totganancia, lucrorentabilidad.ganancia, lucrorentabilidad.rentabilidad FROM ( SELECT salidas.c_cpd, salidas.c_descr, salidas.cant, salidas.costounit, salidas.ventaunit, salidas.totcosto, salidas.totventa, salidas.totganancia, salidas.ganancia, round(salidas.totganancia / totganancia.totganancia * 100, 3) AS rentabilidad FROM ( SELECT movis.c_cpd, movis.c_descr, sum( movis.n_cant-devolvidos(c_cpd,c_nota)) AS cant, round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) / sum(movis.n_cant-devolvidos(c_cpd,c_nota)),2) AS costounit, round(sum( movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) / sum( movis.n_cant-devolvidos(c_cpd,c_nota)),2) AS ventaunit, round(sum( movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))),2) AS totcosto, round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))),2) AS totventa, round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))),2) - round(sum( movis.n_costogs* (n_cant-devolvidos(c_cpd,c_nota) )),2) AS totganancia, round((round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) / sum( movis.n_cant-devolvidos(c_cpd,c_nota)),2) - round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) / sum(movis.n_cant-devolvidos(c_cpd,c_nota)),2)) / round(sum( movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) / sum( movis.n_cant-devolvidos(c_cpd,c_nota)),2) * 100,2) AS ganancia FROM movis WHERE movis.d_emis >= '20070801' and movis.d_emis<='20070821' and c_tipo='V' and movis.n_cant-devolvidos(c_cpd,c_nota)>0 GROUP BY movis.c_cpd, movis.c_descr ORDER BY movis.c_cpd) salidas JOIN ( SELECT sum( movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) - sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) AS totganancia FROM movis WHERE movis.d_emis >= 20070801 and movis.d_emis<='20070821' AND c_tipo='V') totganancia ON TRUE) lucrorentabilidad ORDER BY lucrorentabilidad.c_cpd; Obs: a funcao DEVOLVIDOS() me retorna um numero negativo caso ouve devolucao, do contrario, me retorna 0. CREATE OR REPLACE FUNCTION devolvidos(bpchar, bpchar) RETURNS "numeric" AS $BODY$ select sum(n_cant) from ( select sum(n_cant) as n_cant from movie where c_nota=$2 and c_tipo='D' and c_cpd=$1 union select 0 as n_cant ) as movie; $BODY$ LANGUAGE 'sql' VOLATILE; ALTER FUNCTION devolvidos(bpchar, bpchar) OWNER TO postgres; ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
[pgbr-geral] Consulta muito lenta
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: (
[pgbr-geral] Consulta muito lenta
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 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
Re: [pgbr-geral] Consulta Muito lenta
..:: Rodrigo Machado ::.. wrote: > algum amigo poderia me dar uma mao? > segue o explain da consulta.. e mais a baixo a consulta propiamente. > Você tem certeza que o EXPLAIN informado corresponde a consulta *grande*? Está me parecendo o EXPLAIN do SELECT dentro da função. Não vi nada de anormal no EXPLAIN informado. -- Euler Taveira de Oliveira http://www.timbira.com/ ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Consulta Muito lenta
Rodrigo, Após uma verificação *muito* superficial, segue meu pitaco ... O problema não parece estar na query, mas no numero de queries necessário para calcular devolvidos. Vamos supor que em sua primeira tabela, vc tenha algo em torno de 100 registros... Serão executadas 100 vezes a query da função devolvidos, aparentemente com sequencial scan; Isto sem considerar os joins ... Sugiro que em vez de utilizar uma função vc utilize um join com os registros devolvidos ... Sds, Marco Antonio ..:: Rodrigo Machado ::.. wrote: > Boa tarde amigos, tenho uma consulta que faz referencia a duas tabelas, > uma onde tem os dados que realmente quero consultar, e outra onde > testo se ouve uma devolucao de tal produto... se ouve, ele resta da > coluna quantidade quandos foram devolvidos. > > o problema está que eu recem estou aprendendo, e esta consulta em > alguns clientes com um volumem de dados consideravel, esta demorando > muito... > > algum amigo poderia me dar uma mao? > segue o explain da consulta.. e mais a baixo a consulta propiamente. > > Nested Loop (cost=1828.15..1841.35 rows=330 width=319) > -> Sort (cost=836.84..837.67 rows=330 width=102) > Sort Key: public.movis.c_cpd > -> HashAggregate (cost=759.51..823.04 rows=330 width=102) > -> Index Scan using ind_emismovis on movis > (cost=0.00..619.35 rows=3298 width=102) > Index Cond: ((d_emis >= '2007-08-01'::date) AND > (d_emis <= '2007-08-21'::date)) > Filter: ((c_tipo = 'V'::bpchar) AND ((n_cant - > devolvidos(c_cpd, c_nota)) > 0::numeric)) > -> Materialize (cost=991.31..991.32 rows=1 width=32) > -> Aggregate (cost= 991.27..991.30 rows=1 width=48) > -> Seq Scan on movis (cost=0.00..960.36 rows=6181 > width=48) > Filter: (((d_emis)::text >= '20070801'::text) AND > (d_emis <= '2007-08-21'::date) AND (c_tipo = 'V'::bpchar)) > > > > SELECT lucrorentabilidad.c_cpd, lucrorentabilidad.c_descr, > lucrorentabilidad.cant, lucrorentabilidad.costounit, > lucrorentabilidad.ventaunit, lucrorentabilidad.totcosto, > lucrorentabilidad.totventa, lucrorentabilidad.totganancia , > lucrorentabilidad.ganancia, lucrorentabilidad.rentabilidad > FROM ( SELECT salidas.c_cpd, salidas.c_descr, salidas.cant, > salidas.costounit, salidas.ventaunit, salidas.totcosto, > salidas.totventa, salidas.totganancia , salidas.ganancia, > round(salidas.totganancia / totganancia.totganancia * 100, 3) AS > rentabilidad > FROM ( SELECT movis.c_cpd, movis.c_descr, > sum(movis.n_cant-devolvidos(c_cpd,c_nota)) AS cant, round(sum( > movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) / > sum(movis.n_cant-devolvidos(c_cpd,c_nota)),2) AS costounit, > round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) / > sum(movis.n_cant-devolvidos(c_cpd,c_nota)),2) AS ventaunit, round(sum( > movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))),2) AS totcosto, > round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))),2) AS > totventa, > round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))),2) - > round(sum(movis.n_costogs* (n_cant-devolvidos(c_cpd,c_nota) )),2) AS > totganancia, > round((round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) / > sum(movis.n_cant-devolvidos(c_cpd,c_nota)),2) - > round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) / sum( > movis.n_cant-devolvidos(c_cpd,c_nota)),2)) / > round(sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) / > sum(movis.n_cant-devolvidos(c_cpd,c_nota)),2) * 100,2) AS > ganancia > FROM movis > WHERE movis.d_emis >= '20070801' and > movis.d_emis<='20070821' and c_tipo='V' and > movis.n_cant-devolvidos(c_cpd,c_nota)>0 > GROUP BY movis.c_cpd , > movis.c_descr > ORDER BY movis.c_cpd) salidas JOIN ( SELECT > sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) - > sum(movis.n_costogs*(n_cant-devolvidos(c_cpd,c_nota))) AS > totganancia > >FROM movis > >WHERE movis.d_emis >= 20070801 and movis.d_emis<='20070821' AND > c_tipo='V') totganancia ON TRUE) lucrorentabilidad > >ORDER BY lucrorentabilidad.c_cpd; > > > > Obs: a funcao DEVOLVIDOS() me retorna um numero negativo caso ouve > devolucao, do contrario, me retorna 0. > > CREATE OR REPLACE FUNCTION devolvidos(bpchar, bpchar) > RETURNS "numeric" AS > $BODY$ select sum(n_cant) from ( > select sum(n_cant) as n_cant from movie > where c_nota=$2 and c_tipo='D' and > c_cpd=$1
Re: [pgbr-geral] Consulta Muito lenta
Otima ideia.. é isso que vou fazer.. realmente nao me parecia certo ter q repetir a mesma fucao devolvidos... mas agora eu tenho um problema.. pois eu puxo os dados do movimento de saida do mes de julio por data de emisao... mas alguns itens.. foram devolvidos no mes de agosto.. com a funcao DEVOLVIDOS() eu fazia referencia a nota que tinha sido devolvido.. agora nao vou poder fazer isto... estou pensando a melhor forma de fazer... mas a ideia foi valida.. o mesmo que levava mais de 2 min.. agora caiu p/ 5 segundos.. Obrigado pela ajuda On 8/23/07, Marco A P D´Andrade <[EMAIL PROTECTED]> wrote: > > Rodrigo, > > Após uma verificação *muito* superficial, segue meu pitaco ... > > > O problema não parece estar na query, mas no numero de queries > necessário para calcular devolvidos. > > Vamos supor que em sua primeira tabela, vc tenha algo em torno de 100 > registros... > Serão executadas 100 vezes a query da função devolvidos, aparentemente > com sequencial scan; > Isto sem considerar os joins ... > Sugiro que em vez de utilizar uma função vc utilize um join com os > registros devolvidos ... > > > > Sds, > Marco Antonio > > ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Consulta muito lenta
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
Re: [pgbr-geral] Consulta muito lenta
Fabrízio, As estatísticas estão atualizadas sim, apesar de ser uma base nova rodei um analyze em todas conforme você sugeriu. Obrigado. Em 28 de novembro de 2014 16:19, Fabrízio de Royes Mello < fabri...@timbira.com.br> escreveu: > 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 > >->
Re: [pgbr-geral] Consulta muito lenta
On 28-11-2014 16:40, Ariel Alves wrote: > Fabrízio, > > As estatísticas estão atualizadas sim, apesar de ser uma base nova rodei um > analyze em todas conforme você sugeriu. > Após o ANALYZE algo mudou? Ps: evite top-posting. -- 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
Re: [pgbr-geral] Consulta muito lenta
Ariel, na sua consulta existe mesmo essa sequencia de transformações (cast) concatenando tipos? Porque por exemplo, no trecho: (('2014-11-28'::date)::text || ' '::text) || (tp.departure_time)::text))::timestamp without time zone >= begintimestamp) o custo dessa série de concatenações é maior do que se você fizer assim: ('2014-11-28'::date+tp.departure_time >= begintimestamp) e tem o mesmo efeito, então, daria para avaliar o índice, reavaliando a estrutura da consulta. ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Consulta muito lenta
Em sex, 28 de nov de 2014 19:23, Marcos Thomaz escreveu: > Ariel, na sua consulta existe mesmo essa sequencia de transformações > (cast) concatenando tipos? Porque por exemplo, no trecho: > > (('2014-11-28'::date)::text || ' '::text) || > (tp.departure_time)::text))::timestamp without time zone >= begintimestamp) > > o custo dessa série de concatenações é maior do que se você fizer assim: > > ('2014-11-28'::date+tp.departure_time >= begintimestamp) > > e tem o mesmo efeito, então, daria para avaliar o índice, reavaliando a > estrutura da consulta. > ___ > pgbr-geral mailing list > pgbr-geral@listas.postgresql.org.br > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral > Você pode postar a consulta SQL? ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Consulta muito lenta
On Mon, Apr 6, 2015 at 4:46 PM, Ariel Alves wrote: > -> 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))) > Não analisei a consulta ainda, mas só por esse Index Scan, eu vejo que um índice em (trip_program_id, begintimestamp, endtimestamp) seria útil, você chegou a tentar esse índice? Foi usado na consulta? Qual o plano? Considere também usar o tipo tstzrange ou daterange para essa consulta, assim você pode usar um índice GIN e evitar o "hack" com o COALESCE. Atenciosamente, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Consulta muito lenta
2015-04-06 16:58 GMT-03:00 Matheus de Oliveira : > On Mon, Apr 6, 2015 at 4:46 PM, Ariel Alves > wrote: > >> -> 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))) >> > > Não analisei a consulta ainda, mas só por esse Index Scan, eu vejo que um > índice em (trip_program_id, begintimestamp, endtimestamp) seria útil, você > chegou a tentar esse índice? > Ah, aproveitando, se está mapeando o "endtimestamp" como sendo NULL em casos onde o final não é conhecido, você pode usar o 'infinity', pois timestamp e timestamptz aceitam '-infinity' e 'infinity'. Se quiser testar sem alterar a tabela, você pode usar: ... AND '2015-04-06' BETWEEN begintimestamp AND COALESCE(endtimestamp, 'infinity') E um índice em: (trip_program_id, begintimestamp, COALESCE(endtimestamp, 'infinity')) Atenciosamente, -- Matheus de Oliveira Analista de Banco de Dados Dextra Sistemas - MPS.Br nível F! www.dextra.com.br/postgres ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
Re: [pgbr-geral] Consulta muito lenta
Matheus, Segui as suas sugestões, criei o indice *(trip_program_id, begintimestamp, COALESCE(endtimestamp, 'infinity')**)* que não surtiu muito efeito e em seguida mudei a consulta conforme você sugeriu, neste eu tive um resultado considerável, baixou para 28845.757 ms. 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 '2015-04-06' BETWEEN begintimestamp AND COALESCE(endtimestamp, 'infinity')* group by vbp1.trip_program_id having max(vbp1.vehiclebusserviceplannedid) = vbp2.vehiclebusserviceplannedid)); A titulo de esclarecimento segue os indices que tenho no momento. Indexes: "vehiclebusserviceplanned_pkey" PRIMARY KEY, btree (vehiclebusserviceplannedid) "sugestao_idx" btree (trip_program_id, begintimestamp, (COALESCE(endtimestamp, 'infinity'::timestamp without time zone))) "vehiclebusserviceplanned_busservice_idx" btree (serviceid) "vehiclebusserviceplanned_vehicleid_idx" btree (vehicle_vehicleid) http://explain.depesz.com/s/qlHH No meu humildade entendimento, acredito que isto é até onde conseguimos ir. O próximo passo é mudar na aplicação. Em 6 de abril de 2015 17:06, Matheus de Oliveira escreveu: > > 2015-04-06 16:58 GMT-03:00 Matheus de Oliveira > : > >> On Mon, Apr 6, 2015 at 4:46 PM, Ariel Alves >> wrote: >> >>> -> 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))) >>> >> >> Não analisei a consulta ainda, mas só por esse Index Scan, eu vejo que um >> índice em (trip_program_id, begintimestamp, endtimestamp) seria útil, você >> chegou a tentar esse índice? >> > > Ah, aproveitando, se está mapeando o "endtimestamp" como sendo NULL em > casos onde o final não é conhecido, você pode usar o 'infinity', pois > timestamp e timestamptz aceitam '-infinity' e 'infinity'. Se quiser testar > sem alterar a tabela, você pode usar: > > ... AND '2015-04-06' BETWEEN begintimestamp AND COALESCE(endtimestamp, > 'infinity') > > E um índice em: > > (trip_program_id, begintimestamp, COALESCE(endtimestamp, 'infinity')) > > Atenciosamente, > -- > Matheus de Oliveira > Analista de Banco de Dados > Dextra Sistemas - MPS.Br nível F! > www.dextra.com.br/postgres > > > ___ > pgbr-geral mailing list > pgbr-geral@listas.postgresql.org.br > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral > > -- 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
Re: [pgbr-geral] Consulta muito lenta
É difícil sem ter idéia do que é mais restritivo, mas tenta esse índice em vehiclebusserviceplanned trip_program_id, begintimestamp, COALESCE(endtimestamp, 'infinity'), vehiclebusserviceplannedid Não esquecer de depois de criar o índice fazer um analyze ___ pgbr-geral mailing list pgbr-geral@listas.postgresql.org.br https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral