Re: Partitioned tables and relfilenode

On Thu, Feb 16, 2017 at 6:32 AM, Simon Riggs <simon(at)2ndquadrant(dot)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_e2g3XxdMnSNZMZjuinHRcOB8A@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