Hi all, iam having trouble with a query, in this query we have parameters, to indicate the starting month and the ending month (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=146 loops=1)"
" -> Hash Join (cost=23682.31..23841.15 rows=1 width=130) (actual time=318.809..335.161 rows=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.543 rows=14972 loops=1)"
" -> Seq Scan on receitas (cost=0.00..5033.23 rows=8786 width=24) (actual time=0.236..55.824 rows=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.418 rows=146 loops=1)"
" -> Hash Join (cost=18332.76..18539.12 rows=8 width=126) (actual time=232.701..249.272 rows=146 loops=1)"
" Hash Cond: (qtd.idmov = mv.idmovimento)"
" -> HashAggregate (cost=3716.55..3810.31 rows=7501 width=24) (actual time=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.719 rows=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.85 rows=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=2205 width=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) (actual time=49.178..49.178 rows=49385 loops=1)"
" -> Seq Scan on a_mov_rcb amr (cost=0.00..812.13 rows=49413 width=8) (actual time=0.069..24.160 rows=49385 loops=1)"
" -> Hash (cost=1030.13..1030.13 rows=49313 width=86) (actual time=69.384..69.384 rows=49348 loops=1)"
" -> Seq Scan on recibos rcb (cost=0.00..1030.13 rows=49313 width=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) (actual time=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.002 rows=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.002 rows=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=12586 loops=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.009 rows=15507 loops=1)"
" -> Seq Scan on quantidades (cost=0.00..3526.18 rows=15230 width=24) (actual time=0.203..46.900 rows=15760 loops=1)"
" Filter: (ano = 2009)"
" -> Hash (cost=5250.56..5250.56 rows=2824 width=104) (actual time=96.788..96.788 rows=12597 loops=1)"
" -> Hash Join (cost=5148.19..5250.56 rows=2824 width=104) (actual time=64.588..85.241 rows=12597 loops=1)"
" Hash Cond: (rct.idasso = app.idasso)"
" -> HashAggregate (cost=5099.11..5134.41 rows=2824 width=24) (actual time=62.779..71.578 rows=12597 loops=1)"
" -> Seq Scan on receitas (cost=0.00..5033.23 rows=5271 width=24) (actual time=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=1193 loops=1)"
" -> Nested Loop (cost=0.00..34.16 rows=1193 width=80) (actual time=0.025..1.186 rows=1193 loops=1)"
" -> Seq Scan on parques pq (cost=0.00..1.30 rows=1 width=72) (actual time=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) (actual time=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.005 rows=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.009 rows=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.603 rows=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) (actual time=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) (actual time=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.042 rows=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=49350 loops=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 that would 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
