On Thu, Feb 16, 2017 at 6:32 AM, Simon Riggs <si...@2ndquadrant.com> wrote: > Why not vacuum all partitions? > Why not analyze all partitions? > Truncate all partitions
I agree. But, we need to be careful that a database-wide VACUUM or ANALYZE doesn't hit the partitions multiple times, once for the parent and again for each child. Actually, a database-wide VACUUM should hit each partition individually and do nothing for the parents, but a database-wide ANALYZE should process the parents and do nothing for the children, so that the inheritance statistics get updated. >> - ATRewriteTables() skips on the AlteredTableInfo entries for partitioned >> tables, because there is nothing to be done. >> >> - Since we cannot create indexes on partitioned tables anyway, there is >> no need to handle cluster and reindex (they throw a meaningful error >> already due to the lack of indexes.) > > Create index on all partitions That one's more complicated, per what I wrote in https://www.postgresql.org/message-id/CA+TgmoZUwj=qynak+f7xef4w_e2g3xxdmnsnzmzjuinhrco...@mail.gmail.com > (It also seems like wasted effort to try to remove the overhead caused > by a parent table for partitioning. Why introduce a special case to > save a few bytes? Premature optimization, surely?) I don't think it's wasted effort, actually. My concern isn't so much the empty file on disk (which is stupid, but maybe harmless) as eliminating the dummy scan from the query plan. I believe the do-nothing scan can actually be a noticeable drag on performance in some cases - e.g. if the scan of the partitioned table is on the inside of a nested loop, so that instead of repeatedly doing an index scan on each of 4 partitions, you repeatedly do an index scan on each of 4 partitions and a sequential scan of an empty table. A zero-page sequential scan is pretty fast, but not free. An even bigger problem is that the planner may think that always-empty parent can contain some rows, throwing planner estimates off and messing up the whole plan. We've been living with that problem for a long time, but now that we have an opportunity to fix it, it would be good to do so. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers