[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
