Horton
Menu

Re: mat views stats

Peter Eisentraut <peter.eisentraut@2ndquadrant.com>

View thread
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Jim Mlodgenski <jimmy76@gmail.com>, PgHacker <pgsql-hackers@postgresql.org>
Date: 2017-02-21T22:22:22Z
Lists: pgsql-hackers 
On 2/20/17 10:06, Jim Mlodgenski wrote:
> I've come across a number of times where the statistics on materialized
> views become stale producing bad plans. It turns out that autovacuum
> only touches a materialized view when it is first created and ignores it
> on a refresh. When you have a materialized view like yesterdays_sales
> the data in the materialized view turns over every day. 

That sounds like a bug.

> 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.

I think these should be two separate patches.  We might want to
backpatch the first one.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix REFRESH MATERIALIZED VIEW to report activity to the stats collector.