Re: Declarative partitioning - another take

On Wed, Aug 31, 2016 at 1:05 PM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>> However, it seems a lot better to make it a property of the parent
>> from a performance point of view. Suppose there are 1000 partitions.
>> Reading one toasted value for pg_class and running stringToNode() on
>> it is probably a lot faster than scanning pg_inherits to find all of
>> the child partitions and then doing an index scan to find the pg_class
>> tuple for each and then decoding all of those tuples and assembling
>> them into some data structure.
>
> Seems worth trying. One point that bothers me a bit is how do we enforce
> partition bound condition on individual partition basis. For example when
> a row is inserted into a partition directly, we better check that it does
> not fall outside the bounds and issue an error otherwise. With current
> approach, we just look up a partition's bound from the catalog and gin up
> a check constraint expression (and cache in relcache) to be enforced in
> ExecConstraints(). With the new approach, I guess we would need to look
> up the parent's partition descriptor. Note that the checking in
> ExecConstraints() is turned off when routing a tuple from the parent.

[ Sorry for the slow response. ]

Yeah, that's a problem. Maybe it's best to associate this data with
the childrels after all - or halfway in between, e.g. augment
pg_inherits with this information. After all, the performance problem
I was worried about above isn't really much of an issue: each backend
will build a relcache entry for the parent just once and then use it
for the lifetime of the session unless some invalidation occurs. So
if that takes a small amount of extra time, it's probably not really a
big deal. On the other hand, if we can't build the implicit
constraint for the child table without opening the parent, that's
probably going to cause us some serious inconvenience.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company