![]() It then sorts both tables by the join keys (which means that the data types must be sortable). In a merge join, PostgreSQL picks all join conditions with the = operator. Looking up values in a hash table only works if the operator in the join condition is =, so you need at least one join condition with that operator. In such cases the optimizer usually chooses a different join strategy like a merge join. This is because otherwise PostgreSQL would build the hash in several batches and store them in temporary disk files, which hurts performance. Hash joins are best if none of the involved relations are small, but the hash table for the smaller table fits in work_mem. Since we scan both relations sequentially, an index on the join condition will not help with a hash join. Building the hash table is an extra start-up effort, but probing the hash is much faster than scanning the inner relation. This is somewhat similar to a nested loop join. Then it scans the outer relation sequentially and probes the hash for each row found to find matching join keys. Hash join strategyįirst, PostgreSQL scans the inner relation sequentially and builds a hash table, where the hash key consists of all join keys that use the = operator. So it also serves as a fall-back strategy if no other strategy can be used. ![]() If the outer relation is large, nested loop joins are usually very inefficient, even if they are supported by an index on the inner relation.Īpart from that, it is the only join strategy that can be used if no join condition uses the = operator. It is the typical join strategy used in OLTP workloads with a normalized data model, where it is highly efficient. Nested loop joins are particularly efficient if the outer relation is small, because then the inner loop won’t be executed too often. Use cases for the nested loop join strategy But an index on the join key of the inner relation can speed up a nested loop join considerably. Since we scan the outer relation sequentially, no index on the outer relation will help. Indexes that can help with nested loop joins PostgreSQL scans the outer relation sequentially, and for each result row it scans the inner relation for matching rows. This is the simplest and most general join strategy of all. Note that for inner joins there is no distinction between the join condition and the WHERE condition, but that doesn’t hold for outer joins. I call “ a.col1” and “ b.col2” join keys. ![]() ![]() There are several ways to write a join condition, but all can be transformed to The join condition is a filter that excludes some of these combinations. Join condition and join keyĪ Cartesian product or cross join of two relations is what you get if you combine each row from one relation with each row of the other. We call the upper of the joined relations (in this case the sequential scan on a) the outer relation of the join, and we call the lower relation (the hash computed from b) the inner relation. The execution plan for any join looks like this: The base relation a will be joined to the result of the join of b and c. Such a relation can be a table base relation or the result of any plan node. Terminology RelationĪ join combines data from two relations. 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. If PostgreSQL chooses the wrong strategy, query performance can suffer a lot. ![]() There are three join strategies in PostgreSQL that work quite differently. Join optimizer performance postgresql query ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |