Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id D3A131337B68 for ; Thu, 11 Nov 2010 17:53:10 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 66542-04 for ; Thu, 11 Nov 2010 21:53:04 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-px0-f174.google.com (mail-px0-f174.google.com [209.85.212.174]) by mail.postgresql.org (Postfix) with ESMTP id D73861337B4A for ; Thu, 11 Nov 2010 17:53:02 -0400 (AST) Received: by pxi15 with SMTP id 15so499592pxi.19 for ; Thu, 11 Nov 2010 13:53:02 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:from:content-type :content-transfer-encoding:subject:date:message-id:to:mime-version :x-mailer; bh=85a6mSpjTTl0kZo/+MHaHkEf4QJIiejkmcysdKo6f2E=; b=EXHmI43GQoUXF15ThpQQkNUs7n0WGmpGl6/PWi6eiQP5wYeMYaSLr9DcQqBJHylQp/ MqpFxJF/QLgW/gBLRJBY5O3AkAV0I4OdSXYh9vaM0wUwzdiqHChsorz9tBRAJ0vDU4rh YG8OL4l4pZTg4MKgke0AFdwDdJcBacsVtL/2Y= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=from:content-type:content-transfer-encoding:subject:date:message-id :to:mime-version:x-mailer; b=Syy/mKDasbrDYmZXiO2qEzQzZbUhvjOxwdF0yy0N1EMFv7UghDM6SigPxNewifFXhs sJuzdsaN8hRdCrvkPCXY4jFyVpu0czx0K2xRb+Z21UIhp8Nv/OGYaqMaIF7gGtCb/AOY vM/SIetooQjNrxVTsdgHVXf76sqx+qL4Z8vxU= Received: by 10.142.188.19 with SMTP id l19mr1053014wff.290.1289512382232; Thu, 11 Nov 2010 13:53:02 -0800 (PST) Received: from [192.168.1.102] (adsl-71-138-129-224.dsl.pltn13.pacbell.net [71.138.129.224]) by mx.google.com with ESMTPS id q13sm2882906wfc.17.2010.11.11.13.52.59 (version=TLSv1/SSLv3 cipher=RC4-MD5); Thu, 11 Nov 2010 13:53:00 -0800 (PST) From: Ben Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable Subject: equivalent queries lead to different query plans for self-joins with group by? Date: Thu, 11 Nov 2010 13:52:57 -0800 Message-Id: <5D061DAC-31E2-4C47-AE3B-7799118C57D3@gmail.com> To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Apple Message framework v1081) X-Mailer: Apple Mail (2.1081) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201011/175 X-Sequence-Number: 41199 dear pgers -- consider the following toy example (using pg 8.4.3) : create temporary table foo ( ts timestamp not null, id integer not null, val double precision not null, primary key (ts, id) ); i might want to return the vals, minus the averages at each timestamp. = the obvious self-join results in a sequential scan over foo -- we = aggregate the average val for EVERY timestamp, then join against the = timestamps we want. us_quotedb=3D# explain select ts, id, val - aval from foo join (select = ts, avg(val) as aval from foo group by ts) as a using (ts) where ts > = '2010-11-11' and ts < '2010-11-13'; = =20 = QUERY PLAN = = =20 = --------------------------------------------------------------------------= --------------------------------------------------------------------------= ------- = =20 Hash Join (cost=3D49.06..54.41 rows=3D8 width=3D28) = = = =20 Hash Cond: (pg_temp_2.foo.ts =3D pg_temp_2.foo.ts) = = = =20 -> HashAggregate (cost=3D34.45..36.95 rows=3D200 width=3D16) = = = =20 -> Seq Scan on foo (cost=3D0.00..26.30 rows=3D1630 width=3D16) = = = =20 -> Hash (cost=3D14.51..14.51 rows=3D8 width=3D20) = = = =20 -> Bitmap Heap Scan on foo (cost=3D4.33..14.51 rows=3D8 = width=3D20) = = =20 Recheck Cond: ((ts > '2010-11-11 00:00:00'::timestamp = without time zone) AND (ts < '2010-11-13 00:00:00'::timestamp without = time zone)) = =20 -> Bitmap Index Scan on foo_pkey (cost=3D0.00..4.33 = rows=3D8 width=3D0) = = =20 Index Cond: ((ts > '2010-11-11 00:00:00'::timestamp = without time zone) AND (ts < '2010-11-13 00:00:00'::timestamp without = time zone)) = =20 on the other hand, if i specify "which" timestamp i'm restricting, it = appears to do the right thing: us_quotedb=3D# explain select ts, id, val - aval from foo join (select = ts, avg(val) as aval from foo group by ts) as a using (ts) where a.ts > = '2010-11-11' and a.ts < '2010-11-13'; = =20 = QUERY PLAN = = =20 = --------------------------------------------------------------------------= --------------------------------------------------------------------------= ------- = =20 Nested Loop (cost=3D18.86..29.14 rows=3D8 width=3D28) = = = =20 -> HashAggregate (cost=3D14.55..14.56 rows=3D1 width=3D16) = = = =20 -> Bitmap Heap Scan on foo (cost=3D4.33..14.51 rows=3D8 = width=3D16) = = =20 Recheck Cond: ((ts > '2010-11-11 00:00:00'::timestamp = without time zone) AND (ts < '2010-11-13 00:00:00'::timestamp without = time zone)) = =20 -> Bitmap Index Scan on foo_pkey (cost=3D0.00..4.33 = rows=3D8 width=3D0) = = =20 Index Cond: ((ts > '2010-11-11 00:00:00'::timestamp = without time zone) AND (ts < '2010-11-13 00:00:00'::timestamp without = time zone)) = =20 -> Bitmap Heap Scan on foo (cost=3D4.31..14.45 rows=3D8 width=3D20) = = = =20 Recheck Cond: (pg_temp_2.foo.ts =3D pg_temp_2.foo.ts) = = = =20 -> Bitmap Index Scan on foo_pkey (cost=3D0.00..4.31 rows=3D8 = width=3D0) = = =20 Index Cond: (pg_temp_2.foo.ts =3D pg_temp_2.foo.ts) = = = =20 i find this behavior curious. my understanding is that both queries are = equivalent, and i would expect that the query planner would be able to = choose either of those plans. this is important -- with the real data = i'm working with, the table is very large, and the sequential scan is a = killer.=20 are these queries equivalent, or am i mistaken? if the planner = distinguishes between these plans, how do i ensure that where clause = restrictions propagate (correctly) to subqueries? best regards, ben