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

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