Re: [HACKERS] mat views stats

2017-03-19 Thread Jim Mlodgenski
> > > But that seems pretty ugly. Given the lack of previous reports, I'm > personally content to leave this unfixed in the back branches. > > Comments? > > Most instances of this I've seen out in the field have worked around this by just running analyze in the scheduled jobs after the refresh

Re: [HACKERS] mat views stats

2017-03-18 Thread Tom Lane
Jim Mlodgenski writes: > After digging into things further, just making refresh report the stats > for what is it basically doing simplifies and solves it and it is > something we can back patch if that the consensus. See the attached > patch. I've pushed this into HEAD with

Re: [HACKERS] mat views stats

2017-03-04 Thread Jim Mlodgenski
On Wed, Mar 1, 2017 at 8:39 PM, Michael Paquier wrote: > On Thu, Mar 2, 2017 at 7:20 AM, Jim Mlodgenski wrote: > > > > > > On Sun, Feb 26, 2017 at 11:49 AM, Robert Haas > wrote: > >> > >> On Wed, Feb 22, 2017 at 11:13 AM, Jim

Re: [HACKERS] mat views stats

2017-03-01 Thread Michael Paquier
On Thu, Mar 2, 2017 at 7:20 AM, Jim Mlodgenski wrote: > > > On Sun, Feb 26, 2017 at 11:49 AM, Robert Haas wrote: >> >> On Wed, Feb 22, 2017 at 11:13 AM, Jim Nasby >> wrote: >> > Certainly easier, but I don't think it'd be

Re: [HACKERS] mat views stats

2017-03-01 Thread Jim Mlodgenski
On Sun, Feb 26, 2017 at 11:49 AM, Robert Haas wrote: > On Wed, Feb 22, 2017 at 11:13 AM, Jim Nasby > wrote: > > Certainly easier, but I don't think it'd be better. Matviews really > aren't > > the same thing as tables. Off-hand (without reviewing

Re: [HACKERS] mat views stats

2017-02-26 Thread Robert Haas
On Wed, Feb 22, 2017 at 11:13 AM, Jim Nasby wrote: > 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"

Re: [HACKERS] mat views stats

2017-02-22 Thread Jim Nasby
On 2/22/17 7:56 AM, Peter Eisentraut wrote: What behavior would we like by default? Refreshing a materialized view is a pretty expensive operation, so I think scheduling an analyze quite aggressively right afterwards is often what you want. I think sending a stats message with the number of

Re: [HACKERS] mat views stats

2017-02-22 Thread Peter Eisentraut
On 2/22/17 06:31, Jim Mlodgenski wrote: > 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. But fields like

Re: [HACKERS] mat views stats

2017-02-22 Thread Jim Mlodgenski
On Wed, Feb 22, 2017 at 12:43 AM, Jim Nasby 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

Re: [HACKERS] mat views stats

2017-02-21 Thread Jim Nasby
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.

Re: [HACKERS] mat views stats

2017-02-21 Thread Peter Eisentraut
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

[HACKERS] mat views stats

2017-02-20 Thread Jim Mlodgenski
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