From: Tomas Vondra <tv@fuzzy.cz>
To: Marc Schablewski <ms@clickware.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Partitioning / Strange optimizer behaviour
Date: Mon, 5 Mar 2012 16:20:22 +0100
Message-ID: <552209dfaffc4a1d6934fd5bcf6c5907.squirrel@sq.gransy.com> (raw)
In-Reply-To: <4F54D785.6090200@clickware.de>
References: <4F54D785.6090200@clickware.de>
On 5 Březen 2012, 16:11, Marc Schablewski wrote:
> We have an optimizer problem regarding partitioned tables on 8.4.11.
...
> gdw=> explain select min( emg_id ) from edifactmsgpart;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------
> Aggregate (cost=23521692.03..23521692.04 rows=1 width=8)
> -> Append (cost=0.00..20944139.42 rows=1031021042 width=8)
> -> Seq Scan on edifactmsgpart (cost=0.00..13.70 rows=370
> width=8)
> -> Seq Scan on edifactmsgpart_pact edifactmsgpart
> (cost=0.00..20944125.72 rows=1031020672
> width=8)
>
> I would expect this to run half an hour or so, completely overloading the
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz, ms@clickware.de
Subject: Re: Partitioning / Strange optimizer behaviour
In-Reply-To: <552209dfaffc4a1d6934fd5bcf6c5907.squirrel@sq.gransy.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox