Обсуждение: Optimizer + bind variables
Does/is it possible for the PG optimizer come up with differnet plans when you're using bind variables vs when you send static values? like if my query was select * from users (add a bunch of complex joins) where username = 'dave' vs select * from users (add a bunch of complex joins) where username = '?' In oracle they are frequently different. if it's possible for the plan to be different how can i generate an xplan for the bind version? Thanks! Dave
David Kerr wrote:
> Does/is it possible for the PG optimizer come up with differnet plans when
> you're using bind variables vs when you send static values?
Yes, if the bind variable form causes your DB access driver to use a
server-side prepared statement. Pg can't use its statistics to improve
its query planning if it doesn't have a value for a parameter when it's
building the query plan.
Whether a server-side prepared statement is used or not depends on how
you're connecting to the database - ie your DB access driver and
version. If you're using JDBC, I *think* the JDBC driver does parameter
placement client-side unless you're using a JDBC prepared statement and
the JDBC prepared statement is re-used several times, at which point it
sets up a server-side prepared statement. AFAIK otherwise it uses
client-side (or Pg protocol level) parameter placement.
> if it's possible for the plan to be different how can i generate an
> xplan for the bind version?
xplan = explain? If so:
Use PREPARE to prepare a statement with the params, then use:
EXPLAIN EXECUTE prepared_statement_name(params);
eg:
x=> PREPARE blah AS SELECT * FROM generate_series(1,100);
PREPARE
x=> EXPLAIN EXECUTE blah;
QUERY PLAN
------------------------------------------------------------------------
Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=4)
(1 row)
--
Craig Ringer
On Wed, Nov 04, 2009 at 07:43:16AM +0800, Craig Ringer wrote: - David Kerr wrote: - > Does/is it possible for the PG optimizer come up with differnet plans when - > you're using bind variables vs when you send static values? - - Yes, if the bind variable form causes your DB access driver to use a - server-side prepared statement. Pg can't use its statistics to improve - its query planning if it doesn't have a value for a parameter when it's - building the query plan. hmm, that's a little unclear to me. let's assume that the application is using prepare: Assuming the database hasn't changed, would: PREPARE bla1 as SELECT * from users where username = '$1'; explain execute bla1 give the same output as explain select * from users where username = 'dave'; ? - Whether a server-side prepared statement is used or not depends on how - you're connecting to the database - ie your DB access driver and - version. If you're using JDBC, I *think* the JDBC driver does parameter - placement client-side unless you're using a JDBC prepared statement and - the JDBC prepared statement is re-used several times, at which point it - sets up a server-side prepared statement. AFAIK otherwise it uses - client-side (or Pg protocol level) parameter placement. that's interesting, i'll need to find out which mine are using, probably a mix of both. - > if it's possible for the plan to be different how can i generate an - > xplan for the bind version? - - xplan = explain? If so: yeah, sorry. - Use PREPARE to prepare a statement with the params, then use: - - EXPLAIN EXECUTE prepared_statement_name(params); - - eg: - - x=> PREPARE blah AS SELECT * FROM generate_series(1,100); - PREPARE - x=> EXPLAIN EXECUTE blah; - QUERY PLAN - ------------------------------------------------------------------------ - Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=4) - (1 row) great thanks! Dave
David Kerr wrote: > On Wed, Nov 04, 2009 at 07:43:16AM +0800, Craig Ringer wrote: > - David Kerr wrote: > - > Does/is it possible for the PG optimizer come up with differnet plans when > - > you're using bind variables vs when you send static values? > - > - Yes, if the bind variable form causes your DB access driver to use a > - server-side prepared statement. Pg can't use its statistics to improve > - its query planning if it doesn't have a value for a parameter when it's > - building the query plan. > > hmm, that's a little unclear to me. > > let's assume that the application is using prepare: > > Assuming the database hasn't changed, would: > PREPARE bla1 as SELECT * from users where username = '$1'; > explain execute bla1 > > give the same output as > explain select * from users where username = 'dave'; > > ? No. This is explained in the notes here: http://www.postgresql.org/docs/current/static/sql-prepare.html -- Postgresql & php tutorials http://www.designmagick.com/
On Wed, Nov 04, 2009 at 11:02:22AM +1100, Chris wrote: - David Kerr wrote: - >On Wed, Nov 04, 2009 at 07:43:16AM +0800, Craig Ringer wrote: - >- David Kerr wrote: - No. - - This is explained in the notes here: - - http://www.postgresql.org/docs/current/static/sql-prepare.html <sigh> and i've read that before too. On the upside, then it behaves like I would expect it to, which is good. Thanks Dave
On 11/3/09 4:18 PM, "David Kerr" <dmk@mr-paradox.net> wrote: > On Wed, Nov 04, 2009 at 11:02:22AM +1100, Chris wrote: > - David Kerr wrote: > - >On Wed, Nov 04, 2009 at 07:43:16AM +0800, Craig Ringer wrote: > - >- David Kerr wrote: > - No. > - > - This is explained in the notes here: > - > - http://www.postgresql.org/docs/current/static/sql-prepare.html > > <sigh> and i've read that before too. > > On the upside, then it behaves like I would expect it to, which is > good. > > Thanks > > Dave Note that the query plan can often be the same for the example here. It depends on whether the knowledge of the exact value makes a difference. The most common case is an identifier column. If the column is unique and indexed, and the parameter is an exact = match in the where clause to that column, the plans won't differ. > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >