Message-ID: <4F54D785.6090200@clickware.de>
Date: Mon, 05 Mar 2012 16:11:01 +0100
From: Marc Schablewski <ms@clickware.de>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Partitioning / Strange optimizer behaviour
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

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