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
|
