Skip site navigation (1) Skip section navigation (2)

Re: Partitioning / Strange optimizer behaviour

From: "Tomas Vondra" <tv(at)fuzzy(dot)cz>
To: "Marc Schablewski" <ms(at)clickware(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Partitioning / Strange optimizer behaviour
Date: 2012-03-05 15:20:22
Message-ID: 552209dfaffc4a1d6934fd5bcf6c5907.squirrel@sq.gransy.com (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Marc SchablewskiDate: 2012-03-05 15:44:33
Subject: Re: Partitioning / Strange optimizer behaviour
Previous:From: Marc SchablewskiDate: 2012-03-05 15:11:01
Subject: Partitioning / Strange optimizer behaviour

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group