Menu
Is there a performance penalty when joining two tables in separate schemas (However in the same database).
My physical model has several distinct sections that fit nicely into their own schemas, however I occasionally need to join across schemas and I've been unable to find any information on if this has a negative performance impact.
217
In a merge join, PostgreSQL picks all join conditions with the = operator. It then sorts both tables by the join keys (which means that the data types must be sortable). Then it iterates through both sorted lists and finds matching entries.
If PostgreSQL chooses the wrong strategy, query performance can suffer a lot. This article explains the join strategies, how you can support them with indexes, what can go wrong with them and how you can tune your joins for better performance.
In both cases, a bad row count estimate is the cause of the problem. So while the join may be where we spend most of the execution time, the cause is a misestimate that happened earlier on. Find out what the best join strategy is (perhaps PostgreSQL is doing the right thing anyway).
The PostgreSQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
No, a schema is just a namespace.
Performance might be different when tables (or their indexes) are stored on different tablespaces. One tablespace might be on a fast storage device, the other on a slow one. But that's your choice and has nothing to do with a schema.
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
