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