On Fri, Oct 30, 2009 at 1:18 PM, Jeremy Harris <jgh@xxxxxxxxxxx> wrote: > So, on the becoming more intelligent front: PostgreSQL already does > some operations as background maintenance (autovacuum). Extending > this to de-bloat indices does not seem conceptually impossible It could be done but it's not easy because there will be people concurrently scanning the index. Vacuum is limited to operations it can do without blocking other jobs. >, nor for the collection of table-data statistics for planner guidance Well autovacuum already does this. > (also, why > could a full-table-scan not collect stats as a side-effect?). That's a good idea but there are difficulties with it. The full table scan might not run to completion meaning you may have done a lot of work for nothing. Also gathering and processing that data is fairly expensive, especially for higher stats targets. It requires sorting the data by each column which takes some cpu time which we wouldn't want to make sql queries wait for. > Further out, how about the gathering of statistics on queries to guide the automatic > creation of indices? I think we do need more run-time stats. How to make use of them would be a complex question. We could automatically tune the cost parameters, we could automatically try other plans and see if they run faster, we could even automatically build indexes. Not all of these would be appropriate in every environment though. > Or to set up a partitioning scheme on a previously monolithic table? Well that involves moving data that other users might be busy accessing. Again we wouldn't want an automatic background job blocking user queries. -- greg -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
