Re: Getting an optimal plan on the first execution of a pl/pgsql function

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);