pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizer + bind variables
6+ messages / 4 participants
[nested] [flat]
* Optimizer + bind variables
@ 2009-11-03 17:47 David Kerr <dmk@mr-paradox.net>
0 siblings, 1 reply; 6+ messages in thread
From: David Kerr @ 2009-11-03 17:47 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimizer + bind variables
@ 2009-11-03 23:43 Craig Ringer <craig@postnewspapers.com.au>
parent: David Kerr <dmk@mr-paradox.net>
0 siblings, 1 reply; 6+ messages in thread
From: Craig Ringer @ 2009-11-03 23:43 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimizer + bind variables
@ 2009-11-03 23:52 David Kerr <dmk@mr-paradox.net>
parent: Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 6+ messages in thread
From: David Kerr @ 2009-11-03 23:52 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimizer + bind variables
@ 2009-11-04 00:02 Chris <dmagick@gmail.com>
parent: David Kerr <dmk@mr-paradox.net>
0 siblings, 1 reply; 6+ messages in thread
From: Chris @ 2009-11-04 00:02 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimizer + bind variables
@ 2009-11-04 00:18 David Kerr <dmk@mr-paradox.net>
parent: Chris <dmagick@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: David Kerr @ 2009-11-04 00:18 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Optimizer + bind variables
@ 2009-11-05 05:47 Scott Carey <scott@richrelevance.com>
parent: David Kerr <dmk@mr-paradox.net>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Carey @ 2009-11-05 05:47 UTC (permalink / raw)
To: David Kerr <dmk@mr-paradox.net>; Chris <dmagick@gmail.com>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-performance
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-11-05 05:47 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-03 17:47 Optimizer + bind variables David Kerr <dmk@mr-paradox.net>
2009-11-03 23:43 ` Craig Ringer <craig@postnewspapers.com.au>
2009-11-03 23:52 ` David Kerr <dmk@mr-paradox.net>
2009-11-04 00:02 ` Chris <dmagick@gmail.com>
2009-11-04 00:18 ` David Kerr <dmk@mr-paradox.net>
2009-11-05 05:47 ` Scott Carey <scott@richrelevance.com>
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