From:
Daniel Ferreira
Date:
Hi all, iam having trouble with a query, in this query we have parameters, to indicate the starting month and the
endingmonth (commented line in the query).
You can see the explain using the parameters of month from 1 to 6
EXPLAIN ANALYZE
select pq.nome, mv.data, mv.valor,
gprd.tipo,
tprd.tipo,
prd.nome,
qtd.ano, qtd.mes, qtd.sum,
rct.sum,
rcb.ano, rcb.sigla, rcb.n_recibo, rcb.data, rcb.ncontrib, rcb.nome_cl, rcb.morada_cl, rcb.codpostal_cl
from parques pq,
movimentos mv left join a_mov_rcb amr on mv.idmovimento=amr.idmov left join recibos rcb on
amr.idrecibo=rcb.idrecibo,
(select idmov,ano,mes,idasso,sum(valor) from receitas group by 1,2,3,4) rct,
(select idmov,ano,mes,idasso,sum(quantidade) from quantidades group by 1,2,3,4) qtd,
produtos prd,
tipoprodutos tprd,
grp_prod gprd,
a_prk_prod app
where pq.idparque=mv.idparque
and pq.idparque=10
and rct.ano=2009
and rct.mes between 1 and 6 /* HERE IS THE STARTING AND ENDING MONTH */
and mv.idtipo_mv=21
and mv.vivo
and mv.idmovimento=rct.idmov
and rct.idmov=qtd.idmov
and rct.idasso=qtd.idasso
and rct.ano=qtd.ano
and rct.mes=qtd.mes
and rct.idasso=app.idasso and app.idproduto=prd.idproduto
and prd.idtipoproduto=tprd.idtipoproduto
and prd.idgrp_prod=gprd.idgrp_prod
order by mv.data, prd.idproduto, gprd.idgrp_prod, rcb.sigla, rcb.n_recibo, qtd.ano, qtd.mes
"Sort (cost=23852.81..23852.82 rows=1 width=526) (actual time=339.156..339.197 rows=146 loops=1)"
" Sort Key: mv.data, prd.idproduto, gprd.idgrp_prod, rcb.sigla, rcb.n_recibo, qtd.ano, qtd.mes"
" -> Nested Loop (cost=23682.31..23852.80 rows=1 width=526) (actual time=319.009..338.801 rows=146 loops=1)"
" -> Nested Loop (cost=23682.31..23851.49 rows=1 width=462) (actual time=318.986..337.758 rows=146 loops=1)"
" -> Nested Loop (cost=23682.31..23851.18 rows=1 width=334) (actual time=318.952..337.159 rows=146
loops=1)"
" -> Nested Loop (cost=23682.31..23850.87 rows=1 width=202) (actual time=318.917..336.602 rows=146
loops=1)"
" -> Nested Loop (cost=23682.31..23849.43 rows=1 width=126) (actual time=318.880..335.960
rows=146loops=1)"
" -> Hash Join (cost=23682.31..23841.15 rows=1 width=130) (actual
time=318.809..335.161rows=146 loops=1)"
" Hash Cond: ((rct.idmov = mv.idmovimento) AND (rct.idasso = qtd.idasso) AND
(rct.mes= qtd.mes))"
" -> HashAggregate (cost=5143.05..5201.88 rows=4706 width=24) (actual
time=69.150..79.543rows=14972 loops=1)"
" -> Seq Scan on receitas (cost=0.00..5033.23 rows=8786 width=24) (actual
time=0.236..55.824rows=15668 loops=1)"
" Filter: ((ano = 2009) AND (mes >= 1) AND (mes <= 6))"
" -> Hash (cost=18539.12..18539.12 rows=8 width=126) (actual
time=249.418..249.418rows=146 loops=1)"
" -> Hash Join (cost=18332.76..18539.12 rows=8 width=126) (actual
time=232.701..249.272rows=146 loops=1)"
" Hash Cond: (qtd.idmov = mv.idmovimento)"
" -> HashAggregate (cost=3716.55..3810.31 rows=7501 width=24)
(actualtime=61.735..72.593 rows=15497 loops=1)"
" -> Seq Scan on quantidades (cost=0.00..3526.18 rows=15230
width=24)(actual time=0.223..48.616 rows=15750 loops=1)"
" Filter: (ano = 2009)"
" -> Hash (cost=14588.99..14588.99 rows=2178 width=102) (actual
time=170.719..170.719rows=2559 loops=1)"
" -> Hash Left Join (cost=7052.05..14588.99 rows=2178
width=102)(actual time=166.942..169.261 rows=2559 loops=1)"
" Hash Cond: (amr.idrecibo = rcb.idrecibo)"
" -> Hash Left Join (cost=4706.50..11472.92 rows=2178
width=24)(actual time=77.667..93.502 rows=2559 loops=1)"
" Hash Cond: (mv.idmovimento = amr.idmov)"
" -> Bitmap Heap Scan on movimentos mv
(cost=3058.71..9558.85rows=2178 width=20) (actual time=28.338..35.229 rows=2559 loops=1)"
" Recheck Cond: ((idtipo_mv = 21) AND (10 =
idparque))"
" Filter: vivo"
" -> BitmapAnd (cost=3058.71..3058.71
rows=2205width=0) (actual time=28.196..28.196 rows=0 loops=1)"
" -> Bitmap Index Scan on
idx_03_idtipo_mv (cost=0.00..583.08 rows=33416 width=0) (actual time=7.307..7.307 rows=46019 loops=1)"
" Index Cond: (idtipo_mv = 21)"
" -> Bitmap Index Scan on
idx_02_idparque (cost=0.00..2474.29 rows=141577 width=0) (actual time=19.948..19.948 rows=136676 loops=1)"
" Index Cond: (10 = idparque)"
" -> Hash (cost=812.13..812.13 rows=49413 width=8)
(actualtime=49.178..49.178 rows=49385 loops=1)"
" -> Seq Scan on a_mov_rcb amr
(cost=0.00..812.13rows=49413 width=8) (actual time=0.069..24.160 rows=49385 loops=1)"
" -> Hash (cost=1030.13..1030.13 rows=49313 width=86)
(actualtime=69.384..69.384 rows=49348 loops=1)"
" -> Seq Scan on recibos rcb (cost=0.00..1030.13
rows=49313width=86) (actual time=0.018..31.965 rows=49348 loops=1)"
" -> Index Scan using asso_prk_prod_pkey on a_prk_prod app (cost=0.00..8.27 rows=1
width=8)(actual time=0.003..0.004 rows=1 loops=146)"
" Index Cond: (rct.idasso = app.idasso)"
" -> Index Scan using produtos_pkey on produtos prd (cost=0.00..1.43 rows=1 width=80)
(actualtime=0.002..0.003 rows=1 loops=146)"
" Index Cond: (app.idproduto = prd.idproduto)"
" -> Index Scan using grp_prod_pkey on grp_prod gprd (cost=0.00..0.30 rows=1 width=136) (actual
time=0.002..0.002rows=1 loops=146)"
" Index Cond: (prd.idgrp_prod = gprd.idgrp_prod)"
" -> Index Scan using tipoprodutos_pkey on tipoprodutos tprd (cost=0.00..0.30 rows=1 width=136) (actual
time=0.002..0.002rows=1 loops=146)"
" Index Cond: (prd.idtipoproduto = tprd.idtipoproduto)"
" -> Seq Scan on parques pq (cost=0.00..1.30 rows=1 width=72) (actual time=0.002..0.005 rows=1 loops=146)"
" Filter: (idparque = 10)"
"Total runtime: 339.973 ms"
now here is the explain using the parameters from 1 to 4.
"Sort (cost=23944.24..23944.24 rows=1 width=526) (actual time=1887457.197..1887457.241 rows=124 loops=1)"
" Sort Key: mv.data, prd.idproduto, gprd.idgrp_prod, rcb.sigla, rcb.n_recibo, qtd.ano, qtd.mes"
" -> Nested Loop (cost=16068.57..23944.23 rows=1 width=526) (actual time=34392.436..1887456.339 rows=124 loops=1)"
" Join Filter: (qtd.idmov = mv.idmovimento)"
" -> Nested Loop (cost=9016.52..9328.02 rows=1 width=444) (actual time=156.601..834.424 rows=12586 loops=1)"
" -> Nested Loop (cost=9016.52..9327.70 rows=1 width=316) (actual time=156.572..678.675 rows=12586
loops=1)"
" -> Nested Loop (cost=9016.52..9327.39 rows=1 width=184) (actual time=156.544..579.804 rows=12586
loops=1)"
" -> Hash Join (cost=9016.52..9325.95 rows=1 width=108) (actual time=156.501..304.851
rows=12586loops=1)"
" Hash Cond: ((qtd.idasso = app.idasso) AND (qtd.idmov = rct.idmov) AND (qtd.mes =
rct.mes))"
" -> HashAggregate (cost=3716.55..3810.31 rows=7501 width=24) (actual
time=59.596..106.009rows=15507 loops=1)"
" -> Seq Scan on quantidades (cost=0.00..3526.18 rows=15230 width=24) (actual
time=0.203..46.900rows=15760 loops=1)"
" Filter: (ano = 2009)"
" -> Hash (cost=5250.56..5250.56 rows=2824 width=104) (actual time=96.788..96.788
rows=12597loops=1)"
" -> Hash Join (cost=5148.19..5250.56 rows=2824 width=104) (actual
time=64.588..85.241rows=12597 loops=1)"
" Hash Cond: (rct.idasso = app.idasso)"
" -> HashAggregate (cost=5099.11..5134.41 rows=2824 width=24) (actual
time=62.779..71.578rows=12597 loops=1)"
" -> Seq Scan on receitas (cost=0.00..5033.23 rows=5271 width=24)
(actualtime=11.277..51.444 rows=13173 loops=1)"
" Filter: ((ano = 2009) AND (mes >= 1) AND (mes <= 4))"
" -> Hash (cost=34.16..34.16 rows=1193 width=80) (actual time=1.790..1.790
rows=1193loops=1)"
" -> Nested Loop (cost=0.00..34.16 rows=1193 width=80) (actual
time=0.025..1.186rows=1193 loops=1)"
" -> Seq Scan on parques pq (cost=0.00..1.30 rows=1 width=72)
(actualtime=0.012..0.016 rows=1 loops=1)"
" Filter: (idparque = 10)"
" -> Seq Scan on a_prk_prod app (cost=0.00..20.93 rows=1193
width=8)(actual time=0.006..0.402 rows=1193 loops=1)"
" -> Index Scan using produtos_pkey on produtos prd (cost=0.00..1.43 rows=1 width=80)
(actualtime=0.015..0.016 rows=1 loops=12586)"
" Index Cond: (app.idproduto = prd.idproduto)"
" -> Index Scan using grp_prod_pkey on grp_prod gprd (cost=0.00..0.30 rows=1 width=136) (actual
time=0.003..0.005rows=1 loops=12586)"
" Index Cond: (prd.idgrp_prod = gprd.idgrp_prod)"
" -> Index Scan using tipoprodutos_pkey on tipoprodutos tprd (cost=0.00..0.30 rows=1 width=136) (actual
time=0.003..0.009rows=1 loops=12586)"
" Index Cond: (prd.idtipoproduto = tprd.idtipoproduto)"
" -> Hash Left Join (cost=7052.05..14588.99 rows=2178 width=102) (actual time=146.667..148.973 rows=2559
loops=12586)"
" Hash Cond: (amr.idrecibo = rcb.idrecibo)"
" -> Hash Left Join (cost=4706.50..11472.92 rows=2178 width=24) (actual time=68.974..79.270 rows=2559
loops=12586)"
" Hash Cond: (mv.idmovimento = amr.idmov)"
" -> Bitmap Heap Scan on movimentos mv (cost=3058.71..9558.85 rows=2178 width=20) (actual
time=23.592..25.603rows=2559 loops=12586)"
" Recheck Cond: ((idtipo_mv = 21) AND (10 = idparque))"
" Filter: vivo"
" -> BitmapAnd (cost=3058.71..3058.71 rows=2205 width=0) (actual time=23.474..23.474 rows=0
loops=12586)"
" -> Bitmap Index Scan on idx_03_idtipo_mv (cost=0.00..583.08 rows=33416 width=0)
(actualtime=6.003..6.003 rows=46024 loops=12586)"
" Index Cond: (idtipo_mv = 21)"
" -> Bitmap Index Scan on idx_02_idparque (cost=0.00..2474.29 rows=141577 width=0)
(actualtime=16.570..16.570 rows=136676 loops=12586)"
" Index Cond: (10 = idparque)"
" -> Hash (cost=812.13..812.13 rows=49413 width=8) (actual time=45.280..45.280 rows=49387
loops=12586)"
" -> Seq Scan on a_mov_rcb amr (cost=0.00..812.13 rows=49413 width=8) (actual
time=0.010..21.042rows=49387 loops=12586)"
" -> Hash (cost=1030.13..1030.13 rows=49313 width=86) (actual time=63.760..63.760 rows=49350
loops=12586)"
" -> Seq Scan on recibos rcb (cost=0.00..1030.13 rows=49313 width=86) (actual time=0.006..26.959
rows=49350loops=12586)"
"Total runtime: 1887457.849 ms"
has we can see the query planner, decided to do sequencial scan in "a_mov_rcb" table and "recibos", when i set the flag
"enable_seqscan"to false all goes well.
Anyways i could probably set the "enable_seqscan" always of but i dont know if thats a good idea, because if it was
thatwould be set as off by default.
Is there anything i could do to go around this?
Or can anyone give me a hint why query planner goes sequencial scan when i change the parameters.
Thanks in advanced
From:
Tom Lane
Date:
Daniel Ferreira <> writes:
> has we can see the query planner, decided to do sequencial scan in "a_mov_rcb" table and "recibos", when i set the
flag"enable_seqscan" to false all goes well.
It's not really the seqscan that's the problem. The problem is this
rowcount misestimate:
> " -> Hash Join (cost=9016.52..9325.95 rows=1 width=108) (actual time=156.501..304.851
rows=12586loops=1)"
> " Hash Cond: ((qtd.idasso = app.idasso) AND (qtd.idmov = rct.idmov) AND (qtd.mes =
rct.mes))"
which is causing the planner to suppose that the remaining joins should
be done as nestloops. That would be the right thing if there really
were only one row... with twelve thousand of them, it's taking
twelve thousand times longer than the planner expected.
The right fix would be to get the estimate to be better. (Even if it
were 5 or 10 rows the planner would probably avoid the nestloops.)
But I'm not sure how much you can improve it by raising the stats
targets. This is trying to estimate the size of the join between
two GROUP BY subselects, and the planner is not tremendously good
at that.
A brute force solution might go like this:
1. Select the two GROUP BY sub-results into temp tables.
2. ANALYZE the temp tables.
3. Do the original query using the temp tables.
But it's a pain ...
regards, tom lane