Re: Automating Partitions in PostgreSQL - Query on syntax

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 <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Apr 21, 2009 at 11:50 AM, Csaba Nagy <nagy(at)ecircle-ag(dot)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