PostgreSQL views and materialized views and how they influenced TimescaleDB

Re:PostgreSQL views and materialized views and how they infl... by gopalv on Thursday July 14, 2022 @01:25PM (#32098977)

> 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 something like a "select count(*) + where" or just project 2-3 columns out of a 60+ join view from a couple of tables.

And those just ran forever without the PK-FK removals.

[ Parent | on HN ]

Re:PostgreSQL views and materialized views and how they infl... by michael1999 on Thursday July 14, 2022 @03:25PM (#32100460)

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

[ Parent | on HN ]

Re:PostgreSQL views and materialized views and how they infl... by atwebb on Thursday July 14, 2022 @07:58PM (#32102844)

It is also called Table Elimination if you want a google-able term.

[ Parent | on HN ]

Re:PostgreSQL views and materialized views and how they infl... by jmalicki on Thursday July 14, 2022 @04:05PM (#32100847)

Because of the foreign key, the query should reduce to "explain select count(*) from Department d;"

[ Parent | on HN ]

Re:PostgreSQL views and materialized views and how they infl... by doctor_eval on Thursday July 14, 2022 @05:30PM (#32101689)

The schema only allows one employee per department, so there is no need to look up the employee table.

[ Parent | on HN ]

Re:PostgreSQL views and materialized views and how they infl... by taspeotis on Thursday July 14, 2022 @09:25PM (#32103540)

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

[ Parent | on HN ]

Re:PostgreSQL views and materialized views and how they infl... by mdavidn on Friday July 15, 2022 @12:11AM (#32104530)

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.

[ Parent | on HN ]

Re:PostgreSQL views and materialized views and how they infl... by taspeotis on Friday July 15, 2022 @01:18AM (#32104851)

I have done my own (simple) testing on Postgres 13 and was unable to make Postgres elide the join.

[ Parent | on HN ]