4

I'm developing Pg/PLSQL function for PostgresQL 9.1. When I use variables in a SQL query, optimizer build a bad execution plan. But if I replace a variable by its value the plan is ok. For instance:

v_param := 100;
select count(*)
  into result
  from <some tables>
 where <some conditions>
       and id = v_param

performed in 3s

and

select count(*)
  into result
  from <some tables>
 where <some conditions>
       and id = 100

performed in 300ms

In first case optimizer generate a fixed plan for any value of v_param.

In second case optimizer generate a plan based on specified value and it's significantly more efficient despite not using plan caching.

Is it possible to make optimizer to generate plan without dynamic binding and generate a plan every time when I execute the query?

a.oberon
  • 43
  • 1
  • 4

2 Answers2

8

This has been dramatically improved by Tom Lane in the just-released PostgreSQL 9.2; see What's new in PostgreSQL 9.2 particularly:

Prepared statements used to be optimized once, without any knowledge of the parameters' values. With 9.2, the planner will use specific plans regarding to the parameters sent (the query will be planned at execution), except if the query is executed several times and the planner decides that the generic plan is not too much more expensive than the specific plans.

This has been a long-standing and painful wart that's previously required SET enable_... params, the use of wrapper functions using EXECUTE, or other ugly hacks. Now it should "just work".

Upgrade.

For anyone else reading this, you can tell if this problem is biting you because auto_explain plans of parameterised / prepared queries will differ from those you get when you explain the query yourself. To verify, try PREPARE ... SELECT then EXPLAIN EXECUTE and see if you get a different plan to EXPLAIN SELECT.

See also this prior answer.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • +1 for finding a perfect real-world example of the benefit described in the "What's New" doc which might not have caught everyone's eye. And for being almost ridiculously prompt (<24 hours since the 9.2 GA). – mdahlman Sep 11 '12 at 16:29
  • @mdahlman I've been excited about this feature [since Tom implemented it](http://lwn.net/Articles/497264/) and [pointing it out for a while](http://stackoverflow.com/a/10828675/398670). It's a small but great improvement that should help solve issues like [this](http://archives.postgresql.org/pgsql-general/2011-06/msg00799.php) transparently in future. – Craig Ringer Sep 11 '12 at 21:50
3

Dynamic queries doesn't use cached plans - so you can use EXECUTE USING statement in 9.1 and older. 9.2 should to work without this workaround as Craig wrote.

v_param := 100;
EXECUTE 'select count(*) into result from <some tables> where <some conditions>
   and id = $1' USING v_param;
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • +1 for a workaround that doesn't require upgrading to 9.2 yet. Nice. – mdahlman Sep 11 '12 at 20:53
  • Note that this only works within a PL/PgSQL function. Plain SQL `EXECUTE` is used to invoke prepared statements. I alluded to this in "wrapper functions using `EXECUTE`" but should've been more specific; thanks Pavel. – Craig Ringer Sep 11 '12 at 21:46
  • sure, SQL EXECUTE and PL/pgSQL EXECUTE are two different statements - this technique is not possible for prepared statements and it should be problem in some environments - PostgreSQL has nice functionality for once time execution *PQexecParams*, that can be used instead prepared statements in almost cases, but it is available only in some interfaces – Pavel Stehule Sep 12 '12 at 05:49