Re: Quick question regarding tablespaces

On Thursday 01 July 2004 08:54 pm, Gavin Sherry wrote:
> On Thu, 1 Jul 2004, Mike Rylander wrote:
> > On Thursday 01 July 2004 06:43 pm, Gavin Sherry wrote:
> > > Hi Mike,
> > >
> > > In this release, unfortunately not.
> >
> > That't too bad, but it's not that urgent I suppose.
> >
> > > I had some idea early on of putting rand_page_cost in pg_tablespace and
> > > having the planner have access to it for costing. I didn't actually get
> > > around to it but. :-(
> >
> > Well, I haven't looked at the PG source before, but if you have some
> > specific design ideas I would be glad to help out. I'm just not sure
> > where (or when, with the official release coming (sort of) soon) to
> > start, but with some pointers I'll do what I can!
>
> Well, it wont be in 7.5. Feel free to start looking at how
> random_page_cost in cost_index().

I will start looking there.

> It might be worthwhile introducing a per
> tablespace performance factor so that we could could say that the cost of
> fetching an index tuple from tablespace A is half that of fetching an
> index tuple from tablespace B.

As random_page_cost is tied directly to the performance of a filesystem, my
thought was to leave the setting from the config file as a cluster-wide (and
default tablespace) setting that would be overridden by a tablespace specific
setting... i.e.

ALTER TABLESPACE ... SET RANDOM PAGE COST x.x;

or even setting a scaling factor that would shift the global random page cost.
this scaling factor would be set on all tablespaces and would have a default
of 1. Then it could be set lower ( 0.5 means that tablespace is 2 times
faster than the default tablespace, or global setting). Is that more what
your were thinking?

> That idea might not actually turn out to be
> a very good one once I look at it closely though.
>

If the latter is what you were thinking, I tend to agree. But I think a
direct setting for each tablespace would be a very big benefit. At least I'm
pretty sure I would use it :)

--miker

> Gavin