PostgreSQL views and materialized views and how they influenced TimescaleDB

Re:PostgreSQL views and materialized views and how they infl... by gfody on Thursday July 14, 2022 @12:40PM (#32098333)

> 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 eliminate a left join but not an inner join since the inner join could filter rows (eg symbols in stocks_real_time that don't exist in company (commercial engines will use the presence of fk constraints to know that's impossible and go ahead and eliminate inner joins as well))

[ Parent | on HN ]

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 ]

Re:PostgreSQL views and materialized views and how they infl... by djk447 on Thursday July 14, 2022 @12:54PM (#32098538)

NB: post author here!

Thanks yes! Totally true, was thinking about including some of that but it felt like it opened a can of worms about join types and why certain things would be included and others not (ie inner join needs to see that it's there on both sides whereas the left join doesn't) etc. and the post was already kinda long in the tooth.

[ Parent | on HN ]