Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E1FF26332E6 for ; Tue, 5 Jan 2010 11:03:51 -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 08832-03 for ; Tue, 5 Jan 2010 15:03:32 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f213.google.com (mail-bw0-f213.google.com [209.85.218.213]) by mail.postgresql.org (Postfix) with ESMTP id BF9D663335F for ; Tue, 5 Jan 2010 11:03:40 -0400 (AST) Received: by bwz5 with SMTP id 5so10283629bwz.3 for ; Tue, 05 Jan 2010 07:03:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=JPMDDyM4F37FjrDSQ30dje1fuOMyvWW646O+ROjBEgg=; b=hCI2CTSaasTp/EBMX8hAlIMuZZ6Vqb6PsLv7MeHj3aaxFolwA40eVj2ODcHCXpRcWN LCu442yaqyGpEX3DhOIfV+Y3YHWtD7mJ1x7N4kVj+P6fREeYZEjy+AMIBCj6ONK+dTOS gWuiZ/I8ciwJUOnepdyFgc61osxOGGUef4jVo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=CVMJdRCEckB3C8tsgHCVvipEq1acpH6hSuVSZLawVSr5DnsX7K0GMBwV0r9YuC1sgX G/STEv36D3UIWLvjfGm+70z/GLrOBnrY0Bw9ncoIeenkQl/W6Mvy628OaNtDtoTxewR8 ITeheVRwQobiFQJlOqyDhnLk9m7AWD4Fih4xs= MIME-Version: 1.0 Received: by 10.239.193.139 with SMTP id j11mr364757hbi.127.1262703817829; Tue, 05 Jan 2010 07:03:37 -0800 (PST) In-Reply-To: <4B426AAF.20408@ca.com> References: <4B426AAF.20408@ca.com> Date: Tue, 5 Jan 2010 10:03:37 -0500 Message-ID: <603c8f071001050703g241a3be8i1da20be5f07e670f@mail.gmail.com> Subject: Re: query looping? From: Robert Haas To: Brian Cox Cc: "pgsql-performance@postgresql.org" Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.283 tagged_above=-10 required=5 tests=AWL=-2.720, BAYES_40=-0.185, FH_DATE_PAST_20XX=3.188 X-Spam-Level: X-Archive-Number: 201001/44 X-Sequence-Number: 36924 On Mon, Jan 4, 2010 at 5:24 PM, Brian Cox wrote: > On 01/04/2010 04:53 PM, Robert Haas [robertmhaas@gmail.com] wrote: >> >> PREPARE foo AS >> EXPLAIN EXECUTE foo(); > > Thanks for the response. Results below. Brian > > cemdb=3D> 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 =3D > m.ts_transet_group_id and m.ts_transet_incarnation_id =3D > c.ts_transet_incarnation_id and c.ts_user_incarnation_id =3D > b.ts_user_incarnation_id and c.ts_interval_start_time >=3D $1 and > c.ts_interval_start_time < $2 and b.ts_interval_start_time >=3D $3 and > b.ts_interval_start_time < $4; > PREPARE > > cemdb=3D> explain execute foo('2010-01-03 00:00','2010-01-03 > 08:00','2009-12-28 00:00','2010-01-04 00:00'); > > =A0 =A0 =A0 =A0 QUERY PLAN > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------- > =A0Aggregate =A0(cost=3D325382.51..325382.51 rows=3D1 width=3D8) > =A0 -> =A0Hash Join =A0(cost=3D3486.00..325382.00 rows=3D406 width=3D8) > =A0 =A0 =A0 =A0 Hash Cond: ((b.ts_transet_group_id =3D m.ts_transet_group= _id) AND > (c.ts_transet_incarnation_id =3D m.ts_transet_incarnation_id)) > =A0 =A0 =A0 =A0 -> =A0Hash Join =A0(cost=3D3484.17..325370.84 rows=3D1944= width=3D24) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 Hash Cond: (c.ts_user_incarnation_id =3D > b.ts_user_incarnation_id) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Bitmap Heap Scan on ts_stats_transet_us= er_interval c > =A0(cost=3D2177.34..322486.61 rows=3D96473 width=3D16) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Recheck Cond: ((ts_interval_start= _time >=3D $1) AND > (ts_interval_start_time < $2)) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Bitmap Index Scan on > ts_stats_transet_user_interval_starttime =A0(cost=3D0.00..2165.28 rows=3D= 96473 > width=3D0) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((ts_inte= rval_start_time >=3D $1) AND > (ts_interval_start_time < $2)) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Hash =A0(cost=3D1301.21..1301.21 rows= =3D898 width=3D24) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using > ts_stats_transetgroup_user_weekly_starttimeindex on > ts_stats_transetgroup_user_weekly b =A0(cost=3D0.00..1301.21 rows=3D898 w= idth=3D24) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((ts_inte= rval_start_time >=3D $3) AND > (ts_interval_start_time < $4)) > =A0 =A0 =A0 =A0 -> =A0Hash =A0(cost=3D1.33..1.33 rows=3D67 width=3D16) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Seq Scan on ts_transetgroup_transets_ma= p m > (cost=3D0.00..1.33 rows=3D67 width=3D16) > (14 rows) Hmm. Looks like the same plan. It's not obvious to me what is wrong. Maybe it would make sense to start by checking the row count estimates for the different rows in this plan. For example: 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. ...Robert