[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] Getting an optimal plan on the first execution of a pl/pgsql function
From: Pedro_França <pedro.franca () golsat ! com ! br>
Date: 2015-12-14 20:50:56
Message-ID: CAGWJvi37+FT4VV4u+ac078ezP5HeQz=52KZ+afnwvFO3cmjcMQ () mail ! gmail ! com
[Download RAW message or body]
Thank you for the replies guys, The output of auto-explain pratically
comfirms what you say (sorry there are some portuguese words in there). I
will try pgpooler.
< 2015-12-14 18:10:02.314 BRST >LOG: duration: 0.234 ms plan:
Query Text: SELECT teqp.eqpID,
teqp.eqpveiID AS veiID,
tcb.tcbID,
tvei.veiPlaca,
tvei.veiProprietariocliID,
tcb.tcbtppID,
tcb.tcbVersao,
tvei.veiRPMParametro,
COALESCE(COALESCE(NULLIF(tcb.tcbConfiguracao, 0),
tcc.clcConfiguracaoBitsVeic), 0) AS tcbConfiguracao,
COALESCE(tcb.tcbevtConfig, 0) AS tcbevtConfig,
COALESCE(tvei.veiBitsAlertas, 0) AS veiBitsAlertas,
COALESCE(tvei.veisluID, 0) AS sluID,
COALESCE(tcb.tcbharID, 0) AS harID
FROM TabComputadorBordo tcb
INNER JOIN TabEquipamento teqp ON teqp.eqptcbID = tcb.tcbID
INNER JOIN TabPacoteProduto tpp ON teqp.eqptppID = tpp.tppID
INNER JOIN TabVeiculos tvei ON teqp.eqpveiID = tvei.veiID
LEFT JOIN TabCliente tcli ON tcli.cliid = tvei.veiProprietariocliID
LEFT JOIN TabClienteConfig tcc ON tcc.clcCliID = tcli.cliID
WHERE tcb.tcbserie = $1
AND teqp.eqpAtivo = 1
AND tpp.tppIDProtocolo = $2
AND tvei.veiBloqueioSinal = 0
Nested Loop Left Join (cost=1.29..18.65 rows=1 width=75) (actual
time=0.226..0.230 rows=1 loops=1)
Join Filter: (tcc.clccliid = tcli.cliid)
Rows Removed by Join Filter: 3
-> Nested Loop Left Join (cost=1.29..17.57 rows=1 width=75) (actual
time=0.205..0.209 rows=1 loops=1)
-> Nested Loop (cost=1.01..17.26 rows=1 width=71) (actual
time=0.200..0.203 rows=1 loops=1)
-> Nested Loop (cost=0.72..16.80 rows=1 width=43) (actual
time=0.097..0.098 rows=1 loops=1)
-> Nested Loop (cost=0.58..16.63 rows=1 width=47)
(actual time=0.079..0.080 rows=1 loops=1)
-> Index Scan using
ix_tabcomputadorbordo_tcbserie on tabcomputadorbordo tcb (cost=0.29..8.31
rows=1 width=35) (actual time=0.046..0.046 rows=1 loops=1)
Index Cond: (tcbserie = $1)
-> Index Scan using
ix_tabequipamento_eqptcbid_eqpativo_eqptppid_eqpveiid on tabequipamento
teqp (cost=0.29..8.31 rows=1 width=16) (actual time=0.030..0.031 rows=1
loops=1)
Index Cond: ((eqptcbid = tcb.tcbid) AND
(eqpativo = 1))
-> Index Only Scan using
ix_tabpacoteproduto_tppidprotocolo on tabpacoteproduto tpp
(cost=0.14..0.16 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)
Index Cond: ((tppidprotocolo = $2) AND (tppid =
teqp.eqptppid))
Heap Fetches: 1
-> Index Scan using pk_tabveiculos on tabveiculos tvei
(cost=0.29..0.45 rows=1 width=32) (actual time=0.100..0.101 rows=1 loops=1)
Index Cond: (veiid = teqp.eqpveiid)
Filter: (veibloqueiosinal = 0)
-> Index Only Scan using pk_tabcliente on tabcliente tcli
(cost=0.28..0.30 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)
Index Cond: (cliid = tvei.veiproprietariocliid)
Heap Fetches: 1
-> Seq Scan on tabclienteconfig tcc (cost=0.00..1.03 rows=3 width=8)
(actual time=0.014..0.015 rows=3 loops=1)
< 2015-12-14 18:10:02.314 BRST >CONTEXTO: função PL/pgSQL
ap_keepalive_geteqpid_veiid(bigint,integer) linha 4 em RETURN QUERY
< 2015-12-14 18:10:02.314 BRST >LOG: duration: 4.057 ms plan:
Query Text: SELECT * FROM ap_keepalive_geteqpid_veiid (tcbSerie := 8259492,
protocolo:= 422);
[Attachment #3 (text/html)]
<div dir="ltr"><div class="gmail_extra">Thank you for the replies guys, The output of \
auto-explain pratically comfirms what you say (sorry there are some portuguese words \
in there). I will try pgpooler. </div><div class="gmail_extra"><br></div><div \
class="gmail_extra"><div class="gmail_extra">< 2015-12-14 18:10:02.314 BRST \
>LOG: duration: 0.234 ms plan:</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>Query Text: SELECT <span class="" \
style="white-space:pre"> </span>teqp.eqpID, </div><div class="gmail_extra"><span \
class="" style="white-space:pre"> </span>teqp.eqpveiID AS veiID, </div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span>tcb.tcbID, \
</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>tvei.veiPlaca, </div><div \
class="gmail_extra"><span class="" \
style="white-space:pre"> </span>tvei.veiProprietariocliID, </div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span>tcb.tcbtppID, \
</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>tcb.tcbVersao,</div><div class="gmail_extra"><span \
class="" style="white-space:pre"> </span>tvei.veiRPMParametro, </div><div \
class="gmail_extra"><span class="" \
style="white-space:pre"> </span>COALESCE(COALESCE(NULLIF(tcb.tcbConfiguracao, 0), \
tcc.clcConfiguracaoBitsVeic), 0) AS tcbConfiguracao,</div><div \
class="gmail_extra"><span class="" \
style="white-space:pre"> </span>COALESCE(tcb.tcbevtConfig, 0) AS \
tcbevtConfig,</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>COALESCE(tvei.veiBitsAlertas, 0) AS \
veiBitsAlertas,</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>COALESCE(tvei.veisluID, 0) AS sluID,</div><div \
class="gmail_extra"><span class="" \
style="white-space:pre"> </span>COALESCE(tcb.tcbharID, 0) AS harID</div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span>FROM \
TabComputadorBordo tcb</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>INNER JOIN TabEquipamento teqp ON teqp.eqptcbID = \
tcb.tcbID</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>INNER JOIN TabPacoteProduto tpp ON teqp.eqptppID = \
tpp.tppID</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>INNER JOIN TabVeiculos tvei ON teqp.eqpveiID = \
tvei.veiID</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>LEFT JOIN TabCliente tcli ON tcli.cliid = \
tvei.veiProprietariocliID</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>LEFT JOIN TabClienteConfig tcc ON tcc.clcCliID = \
tcli.cliID</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>WHERE tcb.tcbserie = $1</div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span>AND \
teqp.eqpAtivo = 1</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>AND tpp.tppIDProtocolo = $2</div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span>AND \
tvei.veiBloqueioSinal = 0</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>Nested Loop Left Join (cost=1.29..18.65 rows=1 \
width=75) (actual time=0.226..0.230 rows=1 loops=1)</div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span> Join Filter: \
(tcc.clccliid = tcli.cliid)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> Rows Removed by Join Filter: 3</div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span> -> Nested \
Loop Left Join (cost=1.29..17.57 rows=1 width=75) (actual time=0.205..0.209 rows=1 \
loops=1)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> -> Nested Loop (cost=1.01..17.26 \
rows=1 width=71) (actual time=0.200..0.203 rows=1 loops=1)</div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span> \
-> Nested Loop (cost=0.72..16.80 rows=1 width=43) (actual time=0.097..0.098 \
rows=1 loops=1)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> -> Nested Loop \
(cost=0.58..16.63 rows=1 width=47) (actual time=0.079..0.080 rows=1 \
loops=1)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> -> Index \
Scan using ix_tabcomputadorbordo_tcbserie on tabcomputadorbordo tcb \
(cost=0.29..8.31 rows=1 width=35) (actual time=0.046..0.046 rows=1 loops=1)</div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span> \
Index Cond: (tcbserie = $1)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> -> Index \
Scan using ix_tabequipamento_eqptcbid_eqpativo_eqptppid_eqpveiid on tabequipamento \
teqp (cost=0.29..8.31 rows=1 width=16) (actual time=0.030..0.031 rows=1 \
loops=1)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> Index \
Cond: ((eqptcbid = tcb.tcbid) AND (eqpativo = 1))</div><div class="gmail_extra"><span \
class="" style="white-space:pre"> </span> -> Index \
Only Scan using ix_tabpacoteproduto_tppidprotocolo on tabpacoteproduto tpp \
(cost=0.14..0.16 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)</div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span> \
Index Cond: ((tppidprotocolo = $2) AND (tppid = teqp.eqptppid))</div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span> \
Heap Fetches: 1</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> -> Index Scan using \
pk_tabveiculos on tabveiculos tvei (cost=0.29..0.45 rows=1 width=32) (actual \
time=0.100..0.101 rows=1 loops=1)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> Index Cond: (veiid = \
teqp.eqpveiid)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> Filter: \
(veibloqueiosinal = 0)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> -> Index Only Scan using \
pk_tabcliente on tabcliente tcli (cost=0.28..0.30 rows=1 width=4) (actual \
time=0.004..0.005 rows=1 loops=1)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> Index Cond: (cliid = \
tvei.veiproprietariocliid)</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span> Heap Fetches: 1</div><div \
class="gmail_extra"><span class="" style="white-space:pre"> </span> -> Seq \
Scan on tabclienteconfig tcc (cost=0.00..1.03 rows=3 width=8) (actual \
time=0.014..0.015 rows=3 loops=1)</div><div class="gmail_extra">< 2015-12-14 \
18:10:02.314 BRST >CONTEXTO: função PL/pgSQL \
ap_keepalive_geteqpid_veiid(bigint,integer) linha 4 em RETURN QUERY</div><div \
class="gmail_extra">< 2015-12-14 18:10:02.314 BRST >LOG: duration: 4.057 ms \
plan:</div><div class="gmail_extra"><span class="" \
style="white-space:pre"> </span>Query Text: SELECT * FROM ap_keepalive_geteqpid_veiid \
(tcbSerie := 8259492, protocolo:= 422);</div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic