Re: Declarative partitioning - another take

On Tue, Sep 6, 2016 at 9:19 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Aug 31, 2016 at 1:05 PM, Amit Langote
> <Langote_Amit_f8@lab.ntt.co.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.

Agreed.  So I will stick with the existing approach.

Thanks,
Amit

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers