Re: [GENERAL] Rapid Seek Devices (feature request)
2009/8/17 Jeremy Harris : > Could not pgsql *measure* these costs (on a sampling basis, and with long > time-constants)? In theory, sure. In practice, well, there are some engineering challenges to solve. 1) The cost model isn't perfect so the it's not clear exactly what to measure to get the best result. As the cost model stands now you would have to include some of the cpu time as well as the i/o time. 2) Timing is expensive. On many platforms getting the current time takes a long time and would make a big difference to the runtime of the query. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rapid Seek Devices (feature request)
AFAIK postgresql measure characteristic of the data distribution in the tables and indexes (that is what vacuum ANALYSE does) , but results of that measures are **weighted by** random_page_cost and sequential_page_cost. So measurements are correct, but costs (weight) should reflect a real speed for sequentional and random operation of the storage device(s) (tablespaces) involved. Jeremy Harris napsal(a): On 08/17/2009 03:24 AM, Craig Ringer wrote: On 16/08/2009 9:06 PM, NTPT wrote: So I suggest we should have "random_page_cost" and "Sequential_page_cost" configurable on per tablespace basis. That strikes me as a REALLY good idea, personally, though I don't know enough about the planner to factor in implementation practicalities and any cost for people _not_ using the feature. Could not pgsql *measure* these costs (on a sampling basis, and with long time-constants)? - Jeremy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rapid Seek Devices (feature request)
On 08/17/2009 03:24 AM, Craig Ringer wrote: On 16/08/2009 9:06 PM, NTPT wrote: So I suggest we should have "random_page_cost" and "Sequential_page_cost" configurable on per tablespace basis. That strikes me as a REALLY good idea, personally, though I don't know enough about the planner to factor in implementation practicalities and any cost for people _not_ using the feature. Could not pgsql *measure* these costs (on a sampling basis, and with long time-constants)? - Jeremy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rapid Seek Devices (feature request)
On 16/08/2009 9:06 PM, NTPT wrote: So I suggest we should have "random_page_cost" and "Sequential_page_cost" configurable on per tablespace basis. That strikes me as a REALLY good idea, personally, though I don't know enough about the planner to factor in implementation practicalities and any cost for people _not_ using the feature. 2: Many of that RSD devices are not so much reliable (power outage in ramdisk, weak auxillarity battery in i-ram like devices, block "wear out" in SSD). [snip] ie read from fast tablespace, write to both fast and slow, reconstruct fast from slow if appropriate. This can probably be done as well or better at the OS block layer, using device-mapper or the `md' driver (on Linux). What'd be interesting, though, would be if Pg had support for auto-rebuilding indexes and (if/when explicit support is added) materialized views if it finds the backing files are missing. This would be helpful for such transient devices as RAM disks if you didn't want to bother having physical storage backing it. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
