Re: Stats for inheritance trees

On Tue, Dec 29, 2009 at 3:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
>> Following up on the discussion here
>> http://archives.postgresql.org/message-id/4B3875C6020000250002D97D@gw.wicourts.gov
>> I'd like to propose making the following changes that would allow saner
>> planning for queries involving inheritance:
>
> I've committed the easy aspects of this (still need to work on autovacuum).
> I ran into one unexpected loose end: what shall we do with ALTER TABLE
> SET STATISTICS DISTINCT?
>
> As committed, any manually set value of attdistinct will be applied to
> both the relation-local and inherited stats for the column.  From a
> logical standpoint this is clearly the Wrong Thing, because it's quite
> possible that different values would be needed.  On the other hand,
> I'm not sure how much it matters in practice.  In the typical cases
> where you need to force a value, you're probably going to force a
> fractional value, and those would be likely be OK for both.
>
> The only "real" fix I could see would be to create an additional
> pg_attribute column and a separate command to set it.  But it really
> doesn't seem worth that much trouble.
>
> Or we could think about some heuristics, like applying the manual
> value to inherited stats only if it's fractional (negative).
> But I'm afraid any rule like that would get in the way as often
> as it would help.

Having separate properties for regular attdistinct and inherited
attdistinct seems fairly worthwhile, but I share your lack of
enthusiasm for solving the problem by adding more columns to
pg_attribute. One possibility would be to create a new system catalog
to hold "non-critical" information on pg_attribute properties - that
is, anything that isn't likely to be needed to plan and execute
ordinary queries. attstattarget and attdistinct would certainly
qualify, and there may be others. This would avoid bloating
pg_attribute with things that frequently won't be needed, and as a
side benefit non-bootstrap catalogs are less of a PITA to modify.

A second possibility would be to generalize the concept of reloptions
to apply to columns. Per previous discussion, my per-tablespace
random_page_cost/seq_page_cost patch will generalize reloptions to
apply to tablespaces as well, under the name spcoptions. We could add
attoptions as well, reusing most of the same code and potentially
allowing us to support future options with less recoding. I rather
like this option; it seems like a good fit for any sort of knob that
we want to make available, but don't expect to be used frequently. (I
think, however, that if we're going to do this, I should go ahead and
commit my tablespace patch first, to avoid needless rebase hell.)

These two options aren't completely mutually exclusive; we could
decide that it makes sense to do both, though off the top of my head
it doesn't seem worth the trouble.

...Robert