![]() ![]() Postgres=# explain select * from blogtable1 bt1, blogtable2 bt2 where bt1.id1 = bt2.id1 ![]() Postgres=# create index idx2 on blogtable2(id1) This is because the table data can be retrieved in sorted order because of the index, which is one of the major criteria for the Merge Join method: postgres=# create index idx1 on blogtable1(id1) The example query which resulted in a Hash Join, as shown above, can result in a Merge Join if the index gets created on both tables. The join between relation A and B with condition A.ID = B.ID can be represented as below: For each tuple r in A ![]() This join algorithm is only used if both relations are sorted and join clause operator is “=”. Merge Join is an algorithm wherein each record of outer relation is matched with each record of inner relation until there is a possibility of join clause matching. Here the hash table is created on the table blogtable2 because it is the smaller table so the minimal memory required for hash table and whole hash table can fit in memory. For each tuple r in bucker HashTabĪs per above pre-setup table and data, the following query will result in a Hash Join as shown below: postgres=# explain select * from blogtable1 bt1, blogtable2 bt2 where bt1.id1 = bt2.id1.Insert r into hash table HashTab with key r.ID.The join between relation A and B with condition A.ID = B.ID can be represented as below: Probe Phase: An outer relation record is hashed based on the join clause key to find matching entry in the hash table.The hash key is calculated based on the join clause key. Build Phase: A Hash table is built using the inner relation records.The join in the above example is just a Cartesian product of both tables. Since the join clause is “ Seq Scan on blogtable1 (cost=0.00.145.00 rows=10000 width=8) The Join between relation A and B with condition A.ID Seq Scan on blogtable1 bt1 (cost=0.00.145.00 rows=10000 width=8) Nested Loop Join (NLJ) is the simplest join algorithm wherein each record of outer relation is matched with each record of inner relation. In all our subsequent examples, we consider default configuration parameter unless otherwise specified specifically. Postgres=# insert into blogtable2 values(generate_series(1,1000),3) Postgres=# insert into blogtable1 values(generate_series(1,10000),3) Postgres=# create table blogtable2(id1 int, id2 int) postgres=# create table blogtable1(id1 int, id2 int) Let’s create some pre-setup table and populate with some data, which will be used frequently to better explain these scan methods. ![]() These join methods are implemented by most of the relational databases. query, table data, join clause, selectivity, memory etc. PostgreSQL supports the below kind of joins:Įach of these Join methods are equally useful depending on the query and other parameters e.g. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |