Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 702F36332E6
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  4 Jan 2010 18:26:08 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 17950-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  4 Jan 2010 22:25:52 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp1.ca.com (smtp1.ca.com [141.202.248.21])
	by mail.postgresql.org (Postfix) with ESMTP id E9B1063307A
	for <pgsql-performance@postgresql.org>;
	Mon,  4 Jan 2010 18:25:56 -0400 (AST)
Received: from athena.ca.com ([130.200.164.17]) by smtp1.ca.com with Microsoft
	SMTPSVC(6.0.3790.3959); Mon, 4 Jan 2010 17:25:55 -0500
Message-ID: <4B426AAF.20408@ca.com>
Date: Mon, 04 Jan 2010 14:24:47 -0800
From: Brian Cox <brian.cox@ca.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.5) Gecko/20091204 Lightning/1.0b1 Thunderbird/3.0
MIME-Version: 1.0
To: "Robert Haas [robertmhaas@gmail.com]" <robertmhaas@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: query looping?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-OriginalArrivalTime: 04 Jan 2010 22:25:55.0094 (UTC)
	FILETIME=[E174B360:01CA8D8C]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=2.448 tagged_above=-10 required=5
	tests=BAYES_20=-0.74, FH_DATE_PAST_20XX=3.188
X-Spam-Level: **
X-Archive-Number: 201001/27
X-Sequence-Number: 36907

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)