pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting an optimal plan on the first execution of a pl/pgsql function
4+ messages / 3 participants
[nested] [flat]
* Getting an optimal plan on the first execution of a pl/pgsql function
@ 2015-12-14 18:53 Pedro França <pedro.franca@golsat.com.br>
0 siblings, 1 reply; 4+ messages in thread
From: Pedro França @ 2015-12-14 18:53 UTC (permalink / raw)
To: pgsql-performance
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 10000
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting an optimal plan on the first execution of a pl/pgsql function
@ 2015-12-14 19:21 David G. Johnston <david.g.johnston@gmail.com>
parent: Pedro França <pedro.franca@golsat.com.br>
0 siblings, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2015-12-14 19:21 UTC (permalink / raw)
To: Pedro França <pedro.franca@golsat.com.br>; +Cc: pgsql-performance
On Mon, Dec 14, 2015 at 11:53 AM, Pedro França <pedro.franca@golsat.com.br>
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 10000
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting an optimal plan on the first execution of a pl/pgsql function
@ 2015-12-14 20:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2015-12-14 20:41 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Pedro França <pedro.franca@golsat.com.br>; pgsql-performance
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Dec 14, 2015 at 11:53 AM, Pedro França <pedro.franca@golsat.com.br>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting an optimal plan on the first execution of a pl/pgsql function
@ 2015-12-14 20:50 Pedro França <pedro.franca@golsat.com.br>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Pedro França @ 2015-12-14 20:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance; David G. Johnston <david.g.johnston@gmail.com>
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);
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-12-14 20:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-12-14 18:53 Getting an optimal plan on the first execution of a pl/pgsql function Pedro França <pedro.franca@golsat.com.br>
2015-12-14 19:21 ` David G. Johnston <david.g.johnston@gmail.com>
2015-12-14 20:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-12-14 20:50 ` Pedro França <pedro.franca@golsat.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox