Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 67B1D63247F for ; Tue, 5 Jan 2010 17:34:29 -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 82597-03 for ; Tue, 5 Jan 2010 21:34:14 +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 AB968632433 for ; Tue, 5 Jan 2010 17:34:18 -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:34:16 -0500 Message-ID: <4B43B014.1090003@ca.com> Date: Tue, 05 Jan 2010 13:33:08 -0800 From: Brian Cox 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]" CC: "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:34:16.0761 (UTC) FILETIME=[D51E5A90:01CA8E4E] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.004 tagged_above=-10 required=5 tests=AWL=-0.185, BAYES_50=0.001, FH_DATE_PAST_20XX=3.188 X-Spam-Level: *** X-Archive-Number: 201001/47 X-Sequence-Number: 36927 also compare: [4258-cemdb-admin-2010-01-05 13:11:42.913 PST]LOG: duration: 6401.314 ms statement: execute foo('2010-01-03 00:00','2010-01-03 08:00','2009-12-28 00:00','2010-01-04 00:00'); [4258-cemdb-admin-2010-01-05 13:11:42.913 PST]DETAIL: prepare: 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; still the original query is ~20,000 times slower. Here's the explain foo output for the execute above: 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=347318.10..347318.11 rows=1 width=8) -> Hash Join (cost=3836.14..347317.41 rows=549 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=3834.30..347302.98 rows=2628 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=2199.30..343132.02 rows=103500 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..2186.36 rows=103500 width=0) Index Cond: ((ts_interval_start_time >= $1) AND (ts_interval_start_time < $2)) -> Hash (cost=1627.99..1627.99 rows=1122 width=24) -> Index Scan using ts_stats_transetgroup_user_weekly_starttimeindex on ts_stats_transetgroup_user_weekly b (cost=0.00..1627.99 rows=1122 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) comparing this to the 1st explain foo output shows some minor differences in row estimates -- but nothing, I assume, that could explain the huge time difference. Of course, the 1st plan may not (and probably? wasn't) the plan that was used to take 124M ms. Any thoughts on how to avoid this? Thanks, Brian