5 ms·

> Next Postgres analyzes and rewrites our query, optimizing and simplifying it using a series of complex algorithms. Analyze & rewrite doesn't really use any c

> Next Postgres analyzes and rewrites our query, optimizing and simplifying it using a series of complex algorithms. Analyze & rewrite doesn't really use any complex algorithms. Analyzing basically means that we resolve tables/objects/operators/databases/... in the parse-tree into what they mean in the current database with the current settings. The process of parsing itself (via a bison parser) doesn't do any object lookups and such. So errors about non-existant tables, operators and such will mostly happen during the analyze phase. The rewrite phase, which often will do nothing, will resolve both explicitly (CREATE VIEW) and implicitly (row-level security) referenced views by their definition. It also processes rules in an equivalent manner, but you should never use those... The actual optimization you're referring to will happen as part of planning. EDIT: typos

anonetal 11y ago
Rewrite phase is also supposed to "flatten subqueries/views" (cf. "Extensible/rule based query rewrite optimization in Starburst"; SIGMOD 92) and can be fairly complex. PostgreSQL doesn't do it to the same extent as DB2, but there is certainly some complex logic involved there.
- anarazel 11y ago
  That paper's definition of rewriting isn't the same as postgres' (they even say that: "The work presented here should not be confused with the query rewrite facility of POSTGRES [SJGP90]."). Flattening subqueries and views happens during planning in postgres. Source: I'm a PostgreSQL Developer.
go_prodev 11y ago
Rewrite transforms the sql to use a pre-aggregated materialized view if one exists
- anarazel 11y ago
  PostgreSQL, unfortunately, can't do that. But if it were to happen, it'd not be in the rewriter. It'd not make sense to attempt such transformations before some other optimizations (like flattening of subqueries et al), and that happens in the optimization step

anonetal 11y ago

Rewrite phase is also supposed to "flatten subqueries/views" (cf. "Extensible/rule based query rewrite optimization in Starburst"; SIGMOD 92) and can be fairly complex. PostgreSQL doesn't do it to the same extent as DB2, but there is certainly some complex logic involved there.

anarazel 11y ago
That paper's definition of rewriting isn't the same as postgres' (they even say that: "The work presented here should not be confused with the query rewrite facility of POSTGRES [SJGP90]."). Flattening subqueries and views happens during planning in postgres. Source: I'm a PostgreSQL Developer.

go_prodev 11y ago

Rewrite transforms the sql to use a pre-aggregated materialized view if one exists

anarazel 11y ago
PostgreSQL, unfortunately, can't do that. But if it were to happen, it'd not be in the rewriter. It'd not make sense to attempt such transformations before some other optimizations (like flattening of subqueries et al), and that happens in the optimization step