A SQL Heuristic: ORs Are Expensive

Post by timbowhite » Thu, Oct 02, 2025, 2:30 PM UTC

ethanseal wrote: post #46

johnthescott wrote: post #41

Earlier quoted context omitted.

could you elaborate on pg not really having matviews?

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,…

The pg_ivm plugin adds incremental updates to Postgresql materialized views:

https://github.com/sraoss/pg_ivm

Though I don't know how well it works on a write-heavy production db.

Post by dspillett » Fri, Oct 03, 2025, 9:32 AM UTC

jononor wrote: post #80

dspillett wrote: post #58

Earlier quoted context omitted.

> it would take potentially minutes This is a key part of the problem, and something that people don't realise about query planners. The goal of the planner is not to find the best query plan no matter what, or even to find the best plan at all, it is instead to try to find a good enough plan quickly . The QPs two constraints (find something good enough, do so very quickly) are often diametrically opposed. It must be…

This highlights that there is space for an offline automated query omptimizer/rewriter, to compliment the online query planning?

MS are tinkering with things a little in that direction with recent versions of SQL Server (usually in AzureSQL first then pushed to on-prem releases), though this is mostly around avoiding over-caching. A significant problem with query planners is that to make them better you make them slower, so you introduce caching, but that means you might end up using a bad plan when inputs change.

Re: A SQL Heuristic: ORs Are Expensive

Re: A SQL Heuristic: ORs Are Expensive