3 ms·

I think having a way to build statistics on the join itself would be helpful for this. Similar to how extended statistics^1 can help when column distributions a

I think having a way to build statistics on the join itself would be helpful for this. Similar to how extended statistics^1 can help when column distributions aren't independent of each other. But this may require some basic materialized views, which postgres doesn't really have. [1]: https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-EXTENDED https://www.postgresql.org/docs/current/planner-stats.html#P...

johnthescott 1y ago
could you elaborate on pg not really having matviews?
- ethanseal 1y ago
  Materialized views in Postgres don't update incrementally as the data in the relevant tables updates.^1 In order to keep it up to date, the developer has to tell postgres to refresh the data and postgres will do all the work from scratch. Incremental Materialized views are _hard_. This^2 article goes through how Materialize does it. MSSQL does it really well from what I understand. They only have a few restrictions, though I've never used a MSSQL materialized view in production.^3 [1]: https://www.postgresql.org/docs/current/rules-materializedviews.html https://www.postgresql.org/docs/current/rules-materializedvi... [2]: https://www.scattered-thoughts.net/writing/materialize-decorrelation https://www.scattered-thoughts.net/writing/materialize-decor... [3]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-materialized-view-as-select-transact-sql?view=azure-sqldw-latest#remarks https://learn.microsoft.com/en-us/sql/t-sql/statements/creat...
  - Tostino 1y ago
    As somebody who implemented manual incremental materialized tables using triggers, yeah it's pretty dang hard to make sure you get all the edge cases in which the data can mutate.
  - timbowhite 1y ago
    The pg_ivm plugin adds incremental updates to Postgresql materialized views: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm Though I don't know how well it works on a write-heavy production db.

johnthescott 1y ago

could you elaborate on pg not really having matviews?

ethanseal 1y ago
Materialized views in Postgres don't update incrementally as the data in the relevant tables updates.^1 In order to keep it up to date, the developer has to tell postgres to refresh the data and postgres will do all the work from scratch. Incremental Materialized views are _hard_. This^2 article goes through how Materialize does it. MSSQL does it really well from what I understand. They only have a few restrictions, though I've never used a MSSQL materialized view in production.^3 [1]: https://www.postgresql.org/docs/current/rules-materializedviews.html https://www.postgresql.org/docs/current/rules-materializedvi... [2]: https://www.scattered-thoughts.net/writing/materialize-decorrelation https://www.scattered-thoughts.net/writing/materialize-decor... [3]: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-materialized-view-as-select-transact-sql?view=azure-sqldw-latest#remarks https://learn.microsoft.com/en-us/sql/t-sql/statements/creat...
- Tostino 1y ago
  As somebody who implemented manual incremental materialized tables using triggers, yeah it's pretty dang hard to make sure you get all the edge cases in which the data can mutate.
- timbowhite 1y ago
  The pg_ivm plugin adds incremental updates to Postgresql materialized views: https://github.com/sraoss/pg_ivm https://github.com/sraoss/pg_ivm Though I don't know how well it works on a write-heavy production db.