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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
