Re: Partitioning such that key field of inherited tables no longer retains any selectivity

Tim Kane wrote
> The subject line may not actually describe what I want to illustrate…
>
> Basically, let’s say we have a nicely partitioned data-set. Performance is
> a
> net win and I’m happy with it.
> The partitioning scheme is equality based, rather than range based.
>
> That is, each partition contains a subset of the data where partition_key
> =
> {some_value}, and of course we let constraint exclusion enable the
> optimiser
> to do its thing.
>
> As such, all of the data contained in a given partition has the same value
> for partition_key. That field, within the scope of its partition – isn’t
> terribly useful anymore, and in my mind is wasting bytes – it’s only
> purpose
> really is to allow the CHECK constraint to verify the data is what it
> should
> be.
>
>
> Wouldn’t it be nice if we could somehow create a child table where we
> could
> define a const field value, that did not need to be stored on disk at the
> tuple level?
> This would allow the check constraint to supply the optimiser with the
> information it needs, while removing the need to consume disk to record a
> field whose value is always the same.
>
>
> Extending this idea..
> Postgresql could possibly look at any equality based check constraint for
> a
> table and instead of storing each field value verbatim, we could
> implicitly
> optimise away the need to write those field values to disk, on the
> understanding that those values can never change (unless the constraint is
> removed/altered).
>
> I’m sure there are all kinds of worms in this canister, but I thought it
> might be an interesting discussion.
>
>
> Cheers,
>
> Tim

Two approaches:
1. Standard virtual column name that, when used, gets rewritten into a
constant that is stored at the table level.
2. A way for a column's value to be defined as a function call.

Option 2 has the virtue of being more generally applicable but you'd need
some way to know that for any give table that a given function resolves to a
constant. Maybe have a magic function like partitonid(tabloid) that if used
in a query would be interpreted in this way. Combined with option 1 and the
stand column could be pre-defined in this way - if the partition constant
exists which is the main thing to avoid - increased checking/rewriting time
for non-partitioned tables.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Partitioning-such-that-key-field-of-inherited-tables-no-longer-retains-any-selectivity-tp5803549p5803561.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.