pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitioning / Strange optimizer behaviour
3+ messages / 2 participants
[nested] [flat]
* Partitioning / Strange optimizer behaviour
@ 2012-03-05 15:11 Marc Schablewski <ms@clickware.de>
0 siblings, 1 reply; 3+ messages in thread
From: Marc Schablewski @ 2012-03-05 15:11 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partitioning / Strange optimizer behaviour
@ 2012-03-05 15:20 Tomas Vondra <tv@fuzzy.cz>
parent: Marc Schablewski <ms@clickware.de>
0 siblings, 1 reply; 3+ messages in thread
From: Tomas Vondra @ 2012-03-05 15:20 UTC (permalink / raw)
To: Marc Schablewski <ms@clickware.de>; +Cc: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partitioning / Strange optimizer behaviour
@ 2012-03-05 15:44 Marc Schablewski <ms@clickware.de>
parent: Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 3+ messages in thread
From: Marc Schablewski @ 2012-03-05 15:44 UTC (permalink / raw)
To: Tomas Vondra <tv@fuzzy.cz>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-03-05 15:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-05 15:11 Partitioning / Strange optimizer behaviour Marc Schablewski <ms@clickware.de>
2012-03-05 15:20 ` Tomas Vondra <tv@fuzzy.cz>
2012-03-05 15:44 ` Marc Schablewski <ms@clickware.de>
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