On Wed, Mar 31, 2010 at 12:11 AM, Brian Cox <brian.cox@xxxxxx> wrote:
These won't necessarily get the same plan. If you want to see what plan the prepared query is getting, you'll need to prepare it ("prepare foo as <query>") and then explain *that* via "explain execute foo".
The prepared version likely has a much more generic plan, whereas the regular query gets optimized for the actual values provided.
2010-03-30 18:41:11.685261-07 | select b.ts_id from ts_stats_tranunit_user_daily b, ts_stats_tranunit_user_interval c where b.ts_transet_incarnation_id = c.ts_transet_incarnation_id and b.ts_tranunit_id = c.ts_tranunit_id and b.ts_user_incarnation_id = c.ts_user_incarnation_id and c.ts_interval_start_time >= $1 and c.ts_interval_start_time < $2 and b.ts_interval_start_time >= $3 and b.ts_interval_start_time < $4
(1 row)
about 5 mins later, I, suspecting problems, do (the values are the same as for $1 et al above; EXPLAIN was done on purpose to keep stats [hopefully] the same as when pid 10022 started; there are 80,000 rows in each of the 2 tables at the time of this EXPLAIN and when 10022 started):
cemdb=> explain select b.ts_id from ts_stats_tranunit_user_daily b, ts_stats_tranunit_user_interval c where b.ts_transet_incarnation_id = c.ts_transet_incarnation_id and b.ts_tranunit_id = c.ts_tranunit_id and b.ts_user_incarnation_id = c.ts_user_incarnation_id and c.ts_interval_start_time >= '2010-3-29 01:00' and c.ts_interval_start_time < '2010-3-29 02:00' and b.ts_interval_start_time >= '2010-3-29' and b.ts_interval_start_time < '2010-3-30';
These won't necessarily get the same plan. If you want to see what plan the prepared query is getting, you'll need to prepare it ("prepare foo as <query>") and then explain *that* via "explain execute foo".
The prepared version likely has a much more generic plan, whereas the regular query gets optimized for the actual values provided.
--
- David T. Wilson
david.t.wilson@xxxxxxxxx
