Constraint exclusion-like behavior for UNION ALL views

Поиск
Список
Период
Сортировка
От Tony Cebzanov
Тема Constraint exclusion-like behavior for UNION ALL views
Дата
Msg-id 328ecd35-6cb0-2297-f85e-014a05500d83@gmail.com
обсуждение исходный текст
Ответы Re: Constraint exclusion-like behavior for UNION ALL views
Список pgsql-general
PostgreSQL's materialized view functionality is very useful, but one
problem is that when the view gets large, there is no way to refresh
part of it.  I know that table partitioning is coming in Postgres 10,
but I haven't heard anything about ideas for partitioning / sharding of
materialized views, so I'm trying to work out a strategy for doing it
manually until that's supported in the future.

Because there's no table inheritance for materialized views, the only
way I can think of to be able to get this behavior is to create
materialized views manually for each partition (e.g. each month) and
then use a UNION ALL view to act as the "parent" table.  It looks
something like this:

CREATE MATERIALIZED VIEW prices_2016_04 AS (
         SELECT sale_date, price FROM tbl
         WHERE sale_date >= '2016/04/01' AND sale_date < '2016/05/01'
);

CREATE MATERIALIZED VIEW prices_2016_05 AS (
         SELECT sale_date, price FROM tbl
         WHERE sale_date >= '2016/05/01' AND sale_date < '2016/06/01'
);

CREATE MATERIALIZED VIEW prices_2016_06 AS (
         SELECT sale_date, price FROM tbl
         WHERE sale_date >= '2016/06/01' AND sale_date < '2016/07/01'
);

CREATE VIEW prices_2016 AS (
         SELECT * FROM prices_2016_04
         UNION ALL
         SELECT * FROM prices_2016_05
         UNION ALL
         SELECT * FROM prices_2016_06
);

The problem comes when these views get large.  Unlike when the
underlying objects are tables, I see no way to create CHECK constraints
on these materialized views, which means that queries like:

      SELECT * FROM prices_2016 WHERE sale_date = '2016/04/15'

end up searching through all of the underlying materialized views, even
though the date in the query will only ever match one of them.

As a workaround, I have added logic to my queries to only search tables
for months where the time filters could match, but it would be ideal if
we could create CHECK constraints on materialized views, or if the query
planner could analyze the underlying query that creates the materialized
view to infer that certain rows will never match based on the conditions
that created the view.

Are either of these things that could be supported in the future?  If
not, is there a better way to get this kind of behavior so that
materialized views are more useful when the amount of data increases and
it's not feasible to update them in their entirety?

Thanks.
-Tony


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

Предыдущее
От: Steve Crawford
Дата:
Сообщение: Re: Handling psql lost connections
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: Handling psql lost connections