Re: query looping?

On 01/04/2010 04:53 PM, Robert Haas [robertmhaas(at)gmail(dot)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)