More importantly it makes it a lot harder for the planner to do clever
things. Currently having to append two tables means losing the
ordering of the records and having to resort. Even if that's fixed it
makes it harder to get reasonable estimates for size and distinctness.
Ideally partitioned tables would be completely eliminated from plans
at plan time whenever possible so that the runtime plan is the same as
you would have gotten without partitioning. Where that's not possible
we should aim to get as close as possible.
--
Greg
On 21 Apr 2009, at 18:22, Robert Haas <robertmh...@gmail.com> wrote:
On Tue, Apr 21, 2009 at 11:50 AM, Csaba Nagy <n...@ecircle-ag.com>
wrote:
On Tue, 2009-04-21 at 11:43 -0400, Robert Haas wrote:
This doesn't sound like a very good idea, because the planner cannot
then rely on the overflow table not containing tuples that ought
to be
within some other partition.
The big win that is associated with table partitioning is using
constraint exclusion to avoid unnecessary partitions scans.
Well it could always check 2 partitions: the overflow and the one
selected by the constraint exclusion. If the overflow is kept empty
by
properly setting up the partitions so that all insertions always go
to
one of the active partitions, that would be cheap enough too while
still
providing a way to catch unexpected data. Then when a new partition
is
defined, there's no need to shuffle around data immediately, but
there
could be a maintenance command to clean up the overflow... not to
mention that you could define a trigger to create the new partition
once
you get something in the overflow (how cool would that be if it would
work ?).
Sure, you could do it that way. But it will cause problems for people
who want to have a million rows in each of 100 partitions, and another
million rows in the overflow partition. Now all operations that can
be done on a single partition must scan 2 million rows instead of 1
million, just on the off chance that someone executed a DDL command
and didn't clean up after themselves.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
