PostgreSQL views and materialized views and how they influenced TimescaleDB

Post by djk447 » Thu, Jul 14, 2022, 10:56 PM UTC

djk447 wrote: post #20

hodgesrm wrote: post #19

This is an excellent article. I like the way the author builds up in steps to eventual consistency between source tables and their materialized views. It was fun to guess the next step. I do have one question: how does the algorithm described in the article work when the source table is spread across multiple servers, say in multiple shards? Can TimescaleDB maintain materialized views on each shard and then run a que…

NB: Post author So we thought about doing something like that with multinode where each of the nodes would maintain their own materialization but abandoned it for that very reason it’s very, very difficult to maintain any sort of consistency guarantees in that case, or even to reason about it. Instead we use the access nodes as coordinators to do the materialization. right now the materialization only exists on the a…

Post by atwebb » Thu, Jul 14, 2022, 11:58 PM UTC

michael1999 wrote: post #15

gopalv wrote: post #9

Earlier quoted context omitted.

> commercial engines will use the presence of fk constraints What, postgres doesn't do FK join removals? Like I tried it right now and it didn't remove the hash-join http://sqlfiddle.com/#!17/073747/2 Should've been just a count off the PK Employees table. Apache Calcite in Hive had to bake in this specific optimization because there were a ton of deep join views (coming off Teradata) where someone would run somethin…

How would that work? Your schema allows an employee to be assigned to multiple departments, and the query must count them.

Post by taspeotis » Fri, Jul 15, 2022, 1:25 AM UTC

gopalv wrote: post #9

gfody wrote: post #2

> If I’d written out the query, I might have seen that I didn’t need the JOIN (or never written it in the first place). Whereas the view hides that complexity. So they can make things easier, but that can lead to performance pitfalls if we’re not careful. you can avoid this particular pitfall by using left joins for views like this (that join stuff in for convenience that you might not select) - postgres will elimina…

> commercial engines will use the presence of fk constraints What, postgres doesn't do FK join removals? Like I tried it right now and it didn't remove the hash-join http://sqlfiddle.com/#!17/073747/2 Should've been just a count off the PK Employees table. Apache Calcite in Hive had to bake in this specific optimization because there were a ton of deep join views (coming off Teradata) where someone would run somethin…

Post by Inviz » Fri, Jul 15, 2022, 3:19 AM UTC

Post by taspeotis » Fri, Jul 15, 2022, 5:18 AM UTC

mdavidn wrote: post #28

taspeotis wrote: post #25

Earlier quoted context omitted.

I would try running that fiddle on something more recent than Postgres 9.6

I ran into this recently on a newer version. Postgres will not use the presence of a foreign key (referencing a unique index) to skip unnecessary inner joins.