per table random-page-cost?
in [PgSql]
|
From: Robert Haas on 19 Oct 2009 17:14 On Mon, Oct 19, 2009 at 5:08 PM, marcin mank <marcin.mank(a)gmail.com> wrote: > Currently random_page_cost is a GUC. I propose that this could be set per-table. > > I think this is a good idea for widely-wanted planner hints. This way > You can say "I do NOT want this table to be index-scanned, because I > know it is not cached" by setting it`s random_page_cost to a large > value (an obviously You can do the other way around, when setting the > random_page_cost to 1 You say "I don`t care how You fetch the pages, > they are all in cache") > > The value for the per-table setting could be inferred from > pg_stat(io)?.*tables . We could have a tool to suggest appropriate > values. > > We could call it something like cached_percentage (and have the cost > of a random tuple fetch be inferred from the global random_page_cost, > seq_tuple_cost and the per-table cached_percentage). Then we could set > the global random_page_cost to a sane value like 200. Now one can > wonder why the planner works while having such blantantly unrealistic > values for random_page_cost :) > > What do You think? I've been thinking about this a bit, too. I've been wondering if it might make sense to have a "random_page_cost" and "seq_page_cost" setting for each TABLESPACE, to compensate for the fact that different media might be faster or slower, and a percent-cached setting for each table over top of that. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Greg Stark on 19 Oct 2009 17:27 On Mon, Oct 19, 2009 at 2:08 PM, marcin mank <marcin.mank(a)gmail.com> wrote: > Currently random_page_cost is a GUC. I propose that this could be set per-table. Or per-tablespace. Yes, I think there are a class of GUCs which describe the physical attributes of the storage system which should be per-table or per-tablespace. random_page_cost, sequential_page_cost, effective_io_concurrency come to mind. While this isn't a simple flag to change it does seem like a bit of a SMOP. The GUC infrastructure stores these values in global variables which the planner and other systems consult directly. They would instead have to be made storage parameters which the planner and other systems check on the appropriate table and default to the global GUC if they're not set. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Kevin Grittner" on 19 Oct 2009 17:54 Robert Haas <robertmhaas(a)gmail.com> wrote: > I've been wondering if it might make sense to have a > "random_page_cost" and "seq_page_cost" setting for each TABLESPACE, > to compensate for the fact that different media might be faster or > slower, and a percent-cached setting for each table over top of > that. [after recovering from the initial cringing reaction...] How about calculating an effective percentage based on other information. effective_cache_size, along with relation and database size, come to mind. How about the particular index being considered for the plan? Of course, you might have to be careful about working in TOAST table size for a particular query, based on the columns retrieved. I have no doubt that there would be some major performance regressions in the first cut of anything like this, for at least *some* queries. The toughest part of this might be to get adequate testing to tune it for a wide enough variety of real-life situations. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: marcin mank on 19 Oct 2009 18:17 > I've been thinking about this a bit, too. I've been wondering if it > might make sense to have a "random_page_cost" and "seq_page_cost" > setting for each TABLESPACE, to compensate for the fact that different > media might be faster or slower, and a percent-cached setting for each > table over top of that. > I thought about making it per-table, but realistically I think most people don`t use tablespaces now. I would not want to be telling people "to be able to hint the planner to (not) index-scan the table, You must move it to a separate tablespace". A global default, a per-tablespace default overriding it, and a per-table value overriding them both seems like over-engineering to me. Greetings Marcin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: marcin mank on 19 Oct 2009 18:19
> I thought about making it per-table***space***, but realistically I -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |