Re: Quick question regarding tablespaces - Mailing list pgsql-hackers

On Thursday 01 July 2004 09:26 pm, Scott Marlowe wrote:
> On Thu, 2004-07-01 at 18:54, 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(). 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. That idea might not actually
> > turn out to be a very good one once I look at it closely though.
>
> How about having a per cluster / database / tablespace / table type
> setup that goes in a hierarchy, if they're there.  I.e. if the database
> doesn't have it's own random_page_cost, it inherits from cluster, if a
> tablespace doesn't have one, it inherits from cluster->database, and so
> on to individual tables / indexes.

I was thinking of purely tablespace-based random_page_cost, as that variable 
is tied to the access time of a particular filesystem.

> It may be that it's easier to 
> implement for them all now while doing it for tablespaces.  Just
> wondering.  I'm a user, not a hacker, so I have no idea how much that
> idea makes any sense, but I would certainly love to be able to set an
> index to have a random_page_cost effect of 1.1 while the table it lives
> in is 1.3, the tablespace 1.4, and so on.  But not required, because it
> always inherits from the parent if it doesn't have one, like stats
> target.

I have been thinking about something along the lines of a 'user_cost_push' 
index attribute.  This would default to 1 (if not set) and would be 
multiplied against the cost of the plan node for the index to help or hurt 
the use of the index in cases where the planner consistently makes the wrong 
choice regarding the use of the index (due to funky stats, etc.).

Though perhaps I am just thinking around the problem.  I know there has been 
some pretty big work done on the stats collector recently.

--miker