From: Marc Schablewski <ms@clickware.de>
To: pgsql-performance@postgresql.org
Subject: Partitioning / Strange optimizer behaviour
Date: Mon, 05 Mar 2012 16:11:01 +0100
Message-ID: <4F54D785.6090200@clickware.de> (raw)
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
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: ms@clickware.de
Subject: Re: Partitioning / Strange optimizer behaviour
In-Reply-To: <4F54D785.6090200@clickware.de>
* 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