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

[David G. Johnston]

On Mon, Dec 14, 2015 at 11:53 AM, Pedro França 
wrote:

> I have a really busy function that I need to optimize the best way I can.
> This function is just a nested select statement that is requested several
> times a sec by a legacy application. I'm running a PostgreSQL 9.4 on a
> CentOS 6;
>
> The indexes are in place but I've noticed that it is only used after the
> first execution of the function.
>

​How do you know this?​

I think that the problem is that Postgres isn't getting the best execution
> plan at first because of a parameter that it is highly exclusive in the
> majority of the cases, but it can be not as good sometimes. We can't change
> the way we call the function to a plain sql statement or a view because we
> can't change the application code itself.
>
> When I test with EXPLAIN ANALYZE after the first execution, the query runs
> really fast but the aplication sessions call the function only once and
> then are terminated. I need that the first execution use the actual
> optimized plan.
>
> We tried messing around with the connector driver that manage the
> connection pooling to issue a DISCARD TEMP instead of DISCARD ALL, so it
> could keep the cached plan of the sessions and the performance improved a
> lot, but I don't want to do that in a production environment.
>

Given the constraints you've listed this seems like it might be your only
avenue of improvement.​  Your problem that the performance improvement is
seen due to caching effects.  If you throw away the cache you loose the
improvement.


> I've tried to change the language to a sql function but it didn't help as
> the execution time didn't drop after the first execution.
>

​Yes, this likely would make thing worse...depending upon how it is called.

I've tried to add the "SET LOCAL join_collapse_limit = 1" too but it
> appears it doesn't work inside a function;
>

​I wouldn't expect that parameter to have any effect in this scenario.

Here is the function code:
>
> CREATE OR REPLACE FUNCTION public.ap_keepalive_geteqpid_veiid(
> IN tcbserie bigint,
> IN protocolo integer)
>   RETURNS TABLE(eqpid integer, veiid integer, tcbid integer, veiplaca
> character varying, veiproprietariocliid integer, tcbtppid integer,
> tcbversao character, veirpmparametro double precision, tcbconfiguracao
> bigint, tcbevtconfig integer, veibitsalertas integer, sluid integer, harid
> integer) AS
> $BODY$
> BEGIN
>
> RETURN QUERY
> 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;
>
> END
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 1
>   ROWS 1;
>
> Execution plan in the first execution:
>

​You likely could make this STABLE instead of VOLATILE; though that doesn't
solve your problem.​


> "Function Scan on ap_keepalive_geteqpid_veiid  (cost=0.25..0.26 rows=1
> width=116) (actual time=3.268..3.268 rows=1 loops=1)"
> "Planning time: 0.032 ms"
> "Execution time: 3.288 ms"
>
> Second execution:
>
> "Function Scan on ap_keepalive_geteqpid_veiid  (cost=0.25..0.26 rows=1
> width=116) (actual time=0.401..0.402 rows=1 loops=1)"
> "Planning time: 0.058 ms"
> "Execution time: 0.423 ms"
>
>
​I'm doubting the query inside of the function is the problem here...it is
the function usage itself.  Calling a function has overhead in that the
body of function needs to be processed.  This only has to happen once per
session.  The first call of the function incurs this overhead while
subsequent calls do not.

Pending others correcting me...I fairly certain regarding my conclusions
though somewhat inexperienced in doing this kind of diagnostics.

David J.

[PERFORM] Getting an optimal plan on the first execution of a pl/pgsql function

[Pedro França]

I have a really busy function that I need to optimize the best way I can.
This function is just a nested select statement that is requested several
times a sec by a legacy application. I'm running a PostgreSQL 9.4 on a
CentOS 6;

The indexes are in place but I've noticed that it is only used after the
first execution of the function. I think that the problem is that Postgres
isn't getting the best execution plan at first because of a parameter that
it is highly exclusive in the majority of the cases, but it can be not as
good sometimes. We can't change the way we call the function to a plain sql
statement or a view because we can't change the application code itself.

When I test with EXPLAIN ANALYZE after the first execution, the query runs
really fast but the aplication sessions call the function only once and
then are terminated. I need that the first execution use the actual
optimized plan.

We tried messing around with the connector driver that manage the
connection pooling to issue a DISCARD TEMP instead of DISCARD ALL, so it
could keep the cached plan of the sessions and the performance improved a
lot, but I don't want to do that in a production environment.

I've tried to change the language to a sql function but it didn't help as
the execution time didn't drop after the first execution. I've tried to add
the "SET LOCAL join_collapse_limit = 1" too but it appears it doesn't work
inside a function; Here is the function code:

CREATE OR REPLACE FUNCTION public.ap_keepalive_geteqpid_veiid(
IN tcbserie bigint,
IN protocolo integer)
  RETURNS TABLE(eqpid integer, veiid integer, tcbid integer, veiplaca
character varying, veiproprietariocliid integer, tcbtppid integer,
tcbversao character, veirpmparametro double precision, tcbconfiguracao
bigint, tcbevtconfig integer, veibitsalertas integer, sluid integer, harid
integer) AS
$BODY$
BEGIN

RETURN QUERY
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;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1
  ROWS 1;

Execution plan in the first execution:

"Function Scan on ap_keepalive_geteqpid_veiid  (cost=0.25..0.26 rows=1
width=116) (actual time=3.268..3.268 rows=1 loops=1)"
"Planning time: 0.032 ms"
"Execution time: 3.288 ms"

Second execution:

"Function Scan on ap_keepalive_geteqpid_veiid  (cost=0.25..0.26 rows=1
width=116) (actual time=0.401..0.402 rows=1 loops=1)"
"Planning time: 0.058 ms"
"Execution time: 0.423 ms"

Thank you in advance,
Pedro Ivo

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

[Tom Lane]

"David G. Johnston"  writes:
> On Mon, Dec 14, 2015 at 11:53 AM, Pedro França 
> wrote:
>> When I test with EXPLAIN ANALYZE after the first execution, the query runs
>> really fast but the aplication sessions call the function only once and
>> then are terminated. I need that the first execution use the actual
>> optimized plan.

> Your problem that the performance improvement is
> seen due to caching effects.  If you throw away the cache you loose the
> improvement.

Yeah.  And it's not only the function itself, it's catalog caches and a
bunch of other stuff.  Basically, you should expect that the first few
queries executed by any PG session are going to be slower than those
executed later.  If you can't fix your application to hold sessions open
for a reasonable amount of time, use a connection pooler to do it for you
(pgpooler for instance).

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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

[Pedro França]

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