From: David Wilson <david.t.wilson@gmail.com>
To: Brian Cox <brian.cox@ca.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: query has huge variance in execution times
Date: Wed, 31 Mar 2010 00:37:40 -0400
Message-ID: <x2oe7f9235d1003302137p2f41110bu97dd919cdd4772b@mail.gmail.com> (raw)
In-Reply-To: <4BB2CB78.1020409@ca.com>
References: <4BB2CB78.1020409@ca.com>
On Wed, Mar 31, 2010 at 12:11 AM, Brian Cox <brian.cox@ca.com> wrote:
>
> 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@gmail.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david.t.wilson@gmail.com, brian.cox@ca.com
Subject: Re: query has huge variance in execution times
In-Reply-To: <x2oe7f9235d1003302137p2f41110bu97dd919cdd4772b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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