Обсуждение: Partitioning / Strange optimizer behaviour

We have an optimizer problem regarding partitioned tables on 8.4.11.

We started partitioning a large table containing approx. 1 billion records.

So far, there is only the master table, called edifactmsgpart (which is empty) and 1 partition,
called edifactmsgpart_pact.
There is a bigint column called emg_id with a btree-index on it.

\d edifactmsgpart_pact
...
...    "emp_emg_ept_i_pact" btree (emg_id, ept_id)
...

gdw=> select relname, reltuples from pg_class where relname in( 'edifactmsgpart',
'edifactmsgpart_pact' );
       relname       |  reltuples
---------------------+-------------
 edifactmsgpart_pact | 1.03102e+09
 edifactmsgpart      |           0


a select on the big partition yields a decent plan and performs as expected, lasting only a fraction
of a second.

gdw=> explain select min( emg_id ) from edifactmsgpart_pact;
                                                          QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=2.05..2.06 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..2.05 rows=1 width=8)
           ->  Index Scan using emp_emg_ept_i_pact on edifactmsgpart_pact  (cost=0.00..2109171123.79
rows=1031020672 width=8)
                 Filter: (emg_id IS NOT NULL)


gdw=> select min( emg_id ) from edifactmsgpart_pact;
    min
-----------
 500008178

=>>> very fast.


a select on the partitioned table, however, yields a... shall we call it "sub-optimal" plan:

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?

Kind regards
    Marc

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

Thanks for pointing me to that article. I totally forgot that the postgres wiki existed.

Updating is not an option at the moment, but we'll probably do so in the future. Until then I can
live with the workaround.

Kind regards,
    Marc