Re: mat views stats

On Wed, Feb 22, 2017 at 12:43 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>
wrote:

> On 2/21/17 4:22 PM, Peter Eisentraut wrote:
>
>> Attached is a patch to trigger autovacuum based on a matview refresh
>>> along with a system view pg_stat_all_matviews to show information more
>>> meaningful for materialized views.
>>>
>> It might be easier to include materialized views into pg_stat_*_tables.
>>
>
> Certainly easier, but I don't think it'd be better. Matviews really aren't
> the same thing as tables. Off-hand (without reviewing the patch), update
> and delete counts certainly wouldn't make any sense. "Insert" counts might,
> in as much as it's how many rows have been added by refreshes. You'd want a
> refresh count too.

Matviews already show up in the pg_stat_*_tables and the patch does
leverage the existing pg_stat_*_tables underlying structure, but it creates
more meaningful pg_stat_*_matviews leaving out things like insert and
update counts.

>
>
> I think these should be two separate patches. We might want to
>> backpatch the first one.
>>
>
I was originally thinking 2 patches, but I couldn't think of a way to
trigger the analyze reliably without adding a refresh count or sending
bogus stats. We can certainly send a stats message containing the number of
rows inserted by the refresh, but are we going to also send the number of
deletes as well? Consider a matview that has month to date data. At the end
of the month, there will be about 30n live tuples. The next day on the new
month, there will be n inserts with the stats thinking there are 30n live
tuples which is below the analyze scale factor. We want to analyze the
matview on the first of the day of the new month, but it wouldn't be
triggered for a few days. We can have REFRESH also track live tuples, but
it was quickly becoming a slippery slope of changing behavior for a back
patch. Maybe that's OK and we can go down that road.

We can back patch some documentation about the existing refresh behavior
with autovacuum.