Re: Constraint exclusion-like behavior for UNION ALL views - Mailing list pgsql-general

From Tony Cebzanov
Subject Re: Constraint exclusion-like behavior for UNION ALL views
Date
Msg-id 6d028adb-e83a-2b0c-20d6-8fc972cc983c@gmail.com
Whole thread Raw
In response to Constraint exclusion-like behavior for UNION ALL views  (Tony Cebzanov <tonycpsu@gmail.com>)
List pgsql-general
On 3/29/17 3:39 PM, David G. Johnston wrote:

> That said, I'm not sure what using materialized views instead of normal
> tables buys you in the first place.  I could see possibly using a
> materialized view as the current month's table but the historical tables
> usually don't require refreshing.

My example was simplified for brevity.  The actual materialized views in
question do a significant amount of work, pulling from several other
tables, grouping/aggregating, etc.  It would be possible to have that
same query populate a normal table instead of being stored as a
materialized view, but there's a reason materialized views were created
in the first place -- to avoid the overhead of manually creating
triggers and so forth -- and I was hoping to find a way to retain those
advantages while also being able to partition the views by date.

My thought was that since check constraints already exist for regular
tables, and since materialized views are implemented as tables (or
table-like substances) it seems reasonable that materialized views
should support check constraints and the query optimization that comes
with them.

-Tony



pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Constraint exclusion-like behavior for UNION ALL views
Next
From: Cherio
Date:
Subject: Vacuuming tables with BRIN index and CLUSTER ON index