Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id C73E8632458
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  5 Jan 2010 17:07:19 -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 77468-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  5 Jan 2010 21:07:05 +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 025886324E5
	for <pgsql-performance@postgresql.org>;
	Tue,  5 Jan 2010 17:07:08 -0400 (AST)
Received: from athena.ca.com ([130.200.164.17]) by smtp1.ca.com with Microsoft
	SMTPSVC(6.0.3790.3959); Tue, 5 Jan 2010 16:07:06 -0500
Message-ID: <4B43A9B6.30208@ca.com>
Date: Tue, 05 Jan 2010 13:05:58 -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: 05 Jan 2010 21:07:06.0667 (UTC)
	FILETIME=[0981BFB0:01CA8E4B]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=2.819 tagged_above=-10 required=5 tests=AWL=-0.371,
	BAYES_50=0.001, FH_DATE_PAST_20XX=3.188
X-Spam-Level: **
X-Archive-Number: 201001/46
X-Sequence-Number: 36926

> SELECT SUM(1) FROM ts_stats_transetgroup_user_weekly b WHERE
> ts_interval_start_time > [value] AND ts_interval_start_time < [value];
>
> ...and similarly for the bitmap index scan.
cemdb=> SELECT SUM(1) FROM ts_stats_transetgroup_user_weekly b WHERE 
ts_interval_start_time >= '2009-12-28' AND ts_interval_start_time < 
'2010-01-04';
   sum
-------
  89758
(1 row)

cemdb=> select sum(1) from ts_stats_transet_user_interval where 
ts_interval_start_time >= '2009-01-03' and ts_interval_start_time < 
'2009-01-03 08:00';
  sum
-----

(1 row)

cemdb=> select sum(1) from ts_stats_transet_user_interval where 
ts_interval_start_time >= '2010-01-03' and ts_interval_start_time < 
'2010-01-03 08:00';
   sum
--------
  800000
(1 row)

the estimates in the 1st query plan are OK (since they are the "same"). 
The 2nd, however, look to be too low. FYI, this query finally completed, 
so it wasn't looping but the query plan is very poor:

[24585-cemdb-admin-2010-01-05 10:54:49.511 PST]LOG:  duration: 
124676746.863 ms  execute <unnamed>: 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
[24585-cemdb-admin-2010-01-05 10:54:49.511 PST]DETAIL:  parameters: $1 = 
'2010-01-03 00:00:00-08', $2 = '2010-01-03 08:00:00-08', $3 = 
'2010-01-01 00:00:00-08', $4 = '2010-01-04 00:00:00-08'

compare to:

[root@rdl64xeoserv01 log]# time PGPASSWORD=**** psql -U admin -d cemdb 
-c "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 >= '2010-01-03 
00:00' and c.ts_interval_start_time < '2010-01-03 08:00' and 
b.ts_interval_start_time >= '2009-12-28 00:00' and 
b.ts_interval_start_time < '2010-01-04 00:00'"
  count
-------
  89758
(1 row)


real    0m3.804s
user    0m0.001s
sys     0m0.003s

so why the former ~40,000 times slower?

Thanks,
Brian