From: | "Phil Endecott" <spam_from_pgsql_lists(at)chezphil(dot)org> |
---|---|
To: | <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Advice on using materialized views |
Date: | 2021-12-06 18:48:47 |
Message-ID: | 1638816527952@dmwebmail.dmwebmail.chezphil.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dear Experts,
Does anyone have any suggestions about how to use materialized
views in combination with regular views for the most recent data?
Example:
- I have a raw data table, indexed on a timestamp column, to which
new data is regularly being added.
- I have a set of views that produce hourly/daily/monthly summaries
of this data. Querying these views is slow, so I'd like to make
them materialized views.
- But I'd like the most recent data to be included in the results
of my queries. So I think I need a combined view that is the
union of the materialized view and a non-materialised view for
the recent values.
I can imagine how to implement this but it feels rather clunky;
I need a parallel set of views for the "old" and "new" data, and
I need to modify the definition of a view that filters the "new"
values from the raw table each time the materialised view is
refreshed.
I'm wondering if anyone has an advice or examples of how best
to do this.
Many thanks, Phil.
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2021-12-06 19:19:38 | Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT |
Previous Message | Pavel Stehule | 2021-12-06 18:22:15 | Re: Query planner issue with preferring primary key over a better index when using ORDER BY and LIMIT |