Re: query looping?

On 01/04/2010 04:53 PM, Robert Haas [robertmhaas@gmail.com] wrote:
> PREPARE foo AS <the query, with the $x entries still in there>
> EXPLAIN EXECUTE foo(<the values>);

Thanks for the response. Results below. Brian

cemdb=> prepare foo as select count(distinct b.ts_id) from
ts_stats_transetgroup_user_weekly b, ts_stats_transet_user_interval c,
ts_transetgroup_transets_map m where b.ts_transet_group_id =
m.ts_transet_group_id and m.ts_transet_incarnation_id =
c.ts_transet_incarnation_id and c.ts_user_incarnation_id =
b.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;
PREPARE

cemdb=> explain execute foo('2010-01-03 00:00','2010-01-03
08:00','2009-12-28 00:00','2010-01-04 00:00');

          QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=325382.51..325382.51 rows=1 width=8)
    ->  Hash Join  (cost=3486.00..325382.00 rows=406 width=8)
          Hash Cond: ((b.ts_transet_group_id = m.ts_transet_group_id)
AND (c.ts_transet_incarnation_id = m.ts_transet_incarnation_id))
          ->  Hash Join  (cost=3484.17..325370.84 rows=1944 width=24)
                Hash Cond: (c.ts_user_incarnation_id =
b.ts_user_incarnation_id)
                ->  Bitmap Heap Scan on ts_stats_transet_user_interval c
  (cost=2177.34..322486.61 rows=96473 width=16)
                      Recheck Cond: ((ts_interval_start_time >= $1) AND
(ts_interval_start_time < $2))
                      ->  Bitmap Index Scan on
ts_stats_transet_user_interval_starttime  (cost=0.00..2165.28 rows=96473
width=0)
                            Index Cond: ((ts_interval_start_time >= $1)
AND (ts_interval_start_time < $2))
                ->  Hash  (cost=1301.21..1301.21 rows=898 width=24)
                      ->  Index Scan using
ts_stats_transetgroup_user_weekly_starttimeindex on
ts_stats_transetgroup_user_weekly b  (cost=0.00..1301.21 rows=898 width=24)
                            Index Cond: ((ts_interval_start_time >= $3)
AND (ts_interval_start_time < $4))
          ->  Hash  (cost=1.33..1.33 rows=67 width=16)
                ->  Seq Scan on ts_transetgroup_transets_map m
(cost=0.00..1.33 rows=67 width=16)
(14 rows)