per-tablespace random_page_cost/seq_page_cost

On Mon, Oct 19, 2009 at 6:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Per-table is not physically sensible.  Per-tablespace has some rationale
> to it.

I took a look at this and it seems fairly straightforward. It
basically requires (1) deciding where and how to store per-tablespace
defaults, (2) making those defaults conveniently available to
cost_seqscan(), cost_index(), cost_bitmap_heap_scan(), cost_tidscan(),
and genericcostestimate(), and (3) deciding on some syntax.

As to (1), my thought is to add two new float8 columns to
pg_tablespace. The naming is a little awkward, because
random_page_cost and seq_page_cost would not fit with our (rather odd)
convention for naming system catalog columns. I'm tempted to call
them spcrandompagecost and spcseqpagecost, but I wonder if anyone has
any strong preferences.

As to (2), it looks like we could pretty add reltablespace to
RangeTblEntry (for tables) and IndexOptInfo (for indices). This could
be populated in addRangeTblEntry()/addRangeTblEntryForRelation() for
tables, and in get_relation_info() for indices, essentially for free.
Then the above-mentioned functions that need to use the page costs
could just call a function with a name like
get_tablespace_page_costs() and pass the tablespace OID.

As things stand today, that function would need to scan
pg_tablespace_oid_index to find the correct heap tuple, because there
is no catcache for pg_tablespace entries. I'm not sure whether that's
something that would need to be changed.

As to (3), I was thinking about:

ALTER TABLESPACE name SET ( parameter = value [, ...] )

...where parameter is either seq_page_cost or random_page_cost. The
parentheses are for parity with ALTER TABLE, which employs them so as
to allow change storage parameters and making other table
modifications with a single command. I don't see any immediate need
to support that for ALTER TABLESPACE, which doesn't have many options
at present, but neither do I see any reason to pick a deliberately
incompatible syntax, in case someone wants to implement it in the
future.

Arguably, you would expect parameters set using this syntax to be
stored similar to reloptions - that is, as text[]. But as we're going
to need these values multiple times per table to plan any non-trivial
query, I don't want to inject unnecessary parsing overhead and code
complexity.

Thoughts? Comments? Reservations?

...Robert