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 matioli.math...@gmail.com
escreveu:
2015-04-06 16:58 GMT-03:00 Matheus de Oliveira matioli.math...@gmail.com
:
On Mon, Apr 6, 2015 at 4:46 PM, Ariel Alves arielalves...@gmail.com
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
On Mon, Apr 6, 2015 at 4:46 PM, Ariel Alves arielalves...@gmail.com 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 matioli.math...@gmail.com:
On Mon, Apr 6, 2015 at 4:46 PM, Ariel Alves arielalves...@gmail.com
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
Em sex, 28 de nov de 2014 19:23, Marcos Thomaz marcosthom...@gmail.com
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 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
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
- 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
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
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 union select 0 as n_cant ) as
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
