5

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.

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 creates a general execution plan because of a parameter that it is highly exclusive in the majority of the cases, but it can be not as good sometimes.

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

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 went through the roof, but I don't want to do that in a production environment.

We are on a Postgres 9.4 running on CentOS 6. I've tried running as a SQL function but it didn't help, it was actually faster as a plpgsql func. 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 TabEquipamento teqp
    INNER JOIN TabPacoteProduto tpp ON teqp.eqptppID = tpp.tppID
    INNER JOIN TabComputadorBordo tcb ON teqp.eqptcbID = tcb.tcbID
    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 100 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"

Edit: Added an auto-explain output of the function with a unexpected result (at least for me). The auto-explain claims that postgres executed the function with the desired plain with only 0.230 ms but the function itself took 4.057 ms. I don't know if this is accurate.

< 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);
Improve this question
6
  • Please show us the function and the execution plan for the statement in the function. If the function is "just" a SQL query: did you try a plain SQL function? Or even a view? In general Postgres does not cache execution plans, so it might also be cause by different parameters to the function upon first and subsequent calls.
    – user1822
    Commented Dec 14, 2015 at 11:57
  • I can't turn this into a view because we can't change the app code. The second call of the function is executed with the same parameters as the first call and is way faster. After a DISCARD ALL in the same session, the first execution is slow again.
    – Pivobispo
    Commented Dec 14, 2015 at 12:09
  • Did you try playing with the cost parameter? I am surprised that turning this into a SQL function makes it slower.
    – user1822
    Commented Dec 14, 2015 at 12:19
  • As a sql function, the execution time didnt drop after the first execution so keeping as a plpgsql was better. And the cost parameter didnt help either. Thank you for the replies.
    – Pivobispo
    Commented Dec 14, 2015 at 12:37
  • Why do your parameters default to NULL? (The query hardly makes sense with NULL as input.)
    – Erwin Brandstetter
    Commented Dec 14, 2015 at 16:14

2 Answers 2

Reset to default
2

I cleaned up and simplified a few minor details. That shouldn't change much for performance, yet. But the added SET join_collapse_limit = 1 might:

CREATE OR REPLACE FUNCTION public.ap_keepalive_geteqpid_veiid(tcbserie bigint, protocolo int)
  RETURNS TABLE(eqpid int, veiid int, tcbid int
              , veiplaca varchar, veiproprietariocliid int, tcbtppid int, tcbversao character, veirpmparametro double precision
              , tcbconfiguracao bigint, tcbevtconfig int, veibitsalertas int, sluid int, harid int) AS
$func$
BEGIN
   RETURN QUERY
   SELECT eqp.eqpID
        , eqp.eqpveiID AS veiID
        , cb.tcbID
        , vei.veiPlaca
        , vei.veiProprietariocliID
        , cb.tcbtppID
        , cb.tcbVersao
        , vei.veiRPMParametro
        , CASE WHEN cb.tcbConfiguracao = 0 THEN COALESCE(cc.clcConfiguracaoBitsVeic, 0)
               ELSE cb.tcbConfiguracao END -- AS tcbConfiguracao
        , COALESCE(cb.tcbevtConfig, 0)     -- AS tcbevtConfig
        , COALESCE(vei.veiBitsAlertas, 0)  -- AS veiBitsAlertas
        , COALESCE(vei.veisluID, 0)        -- AS sluID
        , COALESCE(cb.tcbharID, 0)         -- AS harID
   FROM   TabEquipamento        eqp
   JOIN   TabVeiculos           vei ON vei.veiID = eqp.eqpveiID
   JOIN   TabComputadorBordo    cb  ON cb.tcbID  = eqp.eqptcbID
   JOIN   TabPacoteProduto      pp  ON pp.tppID  = eqp.eqptppID
   LEFT   JOIN TabCliente       cli ON cli.cliid = vei.veiProprietariocliID
   LEFT   JOIN TabClienteConfig cc  ON cc.clcCliID = cli.cliID
   WHERE  eqp.eqpAtivo = 1
   AND    vei.veiBloqueioSinal = 0
   AND    cb.tcbserie = $1
   AND    pp.tppIDProtocolo = $2;
END
$func$  LANGUAGE plpgsql VOLATILE STRICT COST 10000 ROWS 1
        SET join_collapse_limit = 1; -- see below!

Notes

COALESCE can take multiple parameters, no need to nest:

COALESCE(NULLIF(cb.tcbConfiguracao, 0), cc.clcConfiguracaoBitsVeic, 0) AS tcbConfiguracao

The CASE expression I ended up using above should be a bit faster, yet.

character is suspicious as data type. It's the same as char(1), I assume you are aware of that.

Inside PL/pgSQL, column aliases that are not referenced in the same query are just for documentation. Only the names in the RETURNS TABLE clause are visible outside the function.

COST 100 is the default for a user-defined function and probably way off for your case. 10000 is probably a better estimate, but unless you nest this function in an outer query, this has hardly any effect.

I removed the parameter defaults (as discussed) and made the function STRICT, since the query would return nothing for any NULL input anyway.

I simplified your table aliases and formatted some more to make it easier to read and play with. That last bit is largely a matter of taste and style.

Answer to question

As to your actual question: There are no (direct) hints for the query planner (optimizer) in PostgreSQL like there are in other RDBMS. Details in the Postgres Wiki on "OptimizerHintsDiscussion".

PL/pgSQL works with prepared statements internally. It will re-plan queries inside the function body with the given input parameters for the first couple of calls per session. Only if it finds that specific plans don't perform better than a generic plan it will then switch to a generic plan and keep that, which saves some overhead.

Details:

However, there are a couple of settings you can adjust. In particular, if you know the best query plan, you can force Postgres to take the order of joins in the FROM clause as given and not try to reorder (which can get expensive for many tables - and you have 6 of them), by setting the join_collapse_limit. This will reduce the cost of planning the query. If done right, it will make the first couple of calls faster. If you mess it up, performance will suffer, of course.

You could put a SET LOCAL as first command:

...
BEGIN
   SET LOCAL join_collapse_limit = 1;
   RETURN QUERY ...

Better yet, declare it as an attribute of the function itself like I did above. The effect of SET LOCAL in the function body would last till the end of the transaction, but, per documentation:

The SET clause causes the specified configuration parameter to be set to the specified value when the function is entered, and then restored to its prior value when the function exits.

Obviously, you need to get the sequence of joins in your FROM clause right yourself. And it has to be good for all possible combinations of parameters. Postgres will not try to optimize. (The STRICT modifier simplifies a bit, because NULL values are excluded now.)

Put tables with the most selective predicates first.

WARNING: Be aware that such optimizations may turn from helpful to obstructive after upgrades to your Postgres version or any major changes in your DB.

Related answer with more details:

Aside

I assume you are aware that there are a couple other possible effects that can make the first call slower than subsequent calls. Like populating cache memory and other things:

Improve this answer
4
  • Thank you for the detailed reply. I've tried to add the "SET LOCAL join_collapse_limit = 1;" after changing the order of the tables in the select statement. The new statement worked as I wanted but only when I executed the query itself but it didn't worked that way inside the function. It performed the same way as before, with the first execution being slow both ways you've suggested. It appears that postgres ignores the setting when in a plpgsql function.
    – Pivobispo
    Commented Dec 14, 2015 at 18:15
  • @Pivobispo: Hmm. You could use auto-explain to log actual query plans from queries inside plpgsql and diagnose more closely where the performance difference comes from. See: dba.stackexchange.com/q/23355/3684. You might then add the query plans obtained this way to your question.
    – Erwin Brandstetter
    Commented Dec 14, 2015 at 19:02
  • Added the auto-explain output of the function execution. Thank you for the tip, didn't know about this module at all.
    – Pivobispo
    Commented Dec 14, 2015 at 20:29
  • 2
    Rather than SET join_collapse_limit inside the function, set it in the function's declaration e.g. create or replace function .... as $$ ... $$ SET join_collapse_limit = 1; . That way the SET doesn't leak to affect queries after the function returns.
    – Craig Ringer
    Commented Dec 14, 2015 at 22:46
1

The guys at the mailing list pgsql-performance told me that the first execution of queries on a session are expected to take more time and suggested me using pgpooler. Maybe thats why the auto-explain said the query took only 0.230 ms when the function itself took 4.057 ms.

Tom Lane wrote:

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.