Re: [HACKERS] mat views stats

Поиск
Список
Период
Сортировка
От Jim Mlodgenski
Тема Re: [HACKERS] mat views stats
Дата
Msg-id CAB_5SRdV75B_3UYMsRMzgmsK9Waeaf4QwKYDnwb=fBM3MBLa8w@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [HACKERS] mat views stats  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Ответы Re: [HACKERS] mat views stats  (Peter Eisentraut <peter.eisentraut@2ndquadrant.com>)
Список pgsql-hackers


On Wed, Feb 22, 2017 at 12:43 AM, Jim Nasby <Jim.Nasby@bluetreble.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.



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Petr Jelinek
Дата:
Сообщение: Re: [HACKERS] Logical Replication WIP
Следующее
От: Petr Jelinek
Дата:
Сообщение: Re: [HACKERS] Logical Replication WIP