Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id D727B159076 for ; Mon, 5 Mar 2012 11:20:59 -0400 (AST) Received: from [89.187.132.199] (helo=elizabeth.gransy.com) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S4Zia-0003CV-Au for pgsql-performance@postgresql.org; Mon, 05 Mar 2012 15:20:58 +0000 Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 7E3E315E4346; Mon, 5 Mar 2012 16:20:22 +0100 (CET) Received: from 62.40.76.70 (proxying for unknown) (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Mon, 5 Mar 2012 16:20:22 +0100 Message-ID: <552209dfaffc4a1d6934fd5bcf6c5907.squirrel@sq.gransy.com> In-Reply-To: <4F54D785.6090200@clickware.de> References: <4F54D785.6090200@clickware.de> Date: Mon, 5 Mar 2012 16:20:22 +0100 Subject: Re: Partitioning / Strange optimizer behaviour From: "Tomas Vondra" To: "Marc Schablewski" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 X-Priority: 3 (Normal) Importance: Normal Content-Transfer-Encoding: quoted-printable X-Host-Lookup-Failed: Reverse DNS lookup failed for 89.187.132.199 (deferred) X-Pg-Spam-Score: 0.3 (/) X-Archive-Number: 201203/55 X-Sequence-Number: 46505 On 5 B=C5=99ezen 2012, 16:11, Marc Schablewski wrote: > We have an optimizer problem regarding partitioned tables on 8.4.11. ... > gdw=3D> explain select min( emg_id ) from edifactmsgpart; > QUERY PLAN > -----------------------------------------------------------------------= --------------------------------------- > Aggregate (cost=3D23521692.03..23521692.04 rows=3D1 width=3D8) > -> Append (cost=3D0.00..20944139.42 rows=3D1031021042 width=3D8) > -> Seq Scan on edifactmsgpart (cost=3D0.00..13.70 rows=3D370 > width=3D8) > -> Seq Scan on edifactmsgpart_pact edifactmsgpart > (cost=3D0.00..20944125.72 rows=3D1031020672 > width=3D8) > > I would expect this to run half an hour or so, completely overloading t= he > server... > > Any Ideas? This is a well known "feature" of pre-9.1 releases - it simply does not handle min/max on partitioned tables well. There's even an example of a workaround on the wiki: https://wiki.postgresql.org/wiki/Efficient_min/max_over_partitioned_table Another option is to upgrade to 9.1 which handles this fine. Tomas