![]() A join between an unpartitioned table and a partitioned table can be executed using this technique by joining the unpartitioned table with each partition separately and combining the results of these joins.Even if the partition bounds do not match exactly, the technique can be used when every partition in one partitioned table has at most one matching partition from the other partitioned table.In the basic version that was committed, the technique is applied when the joining tables have exactly the same partition key data types and have exactly matching partition bounds. We will talk about performance more in a follow-on post. Partition-wise join wins over unpartitioned join because it can take advantage of properties of the partitions and use smaller hash tables that may completely fit in memory, faster in-memory sorts, join push-down in case of foreign partitions and so on. Eliminating an entire partition is a significant improvement, since sequential scans are expensive. With partition-wise join, it sensed the lack of a matching partition and eliminated a scan on prt1_p3 as well. ![]() But it didn't notice that no row in prt1_p3 had a join partner at all and still scanned that partition. ![]() The condition t2.b between 0 eliminated partition prt2_p3 so it does not get scanned by the plan without partition-wise join.For example, the join between prt1_p2 and prt2_p2 uses nested loop join with index scan on prt2_p2_b as parameterized inner side, whereas the other join uses hash join. Without partition-wise join, it used hash join, but with partition-wise join it used different strategies for each join between partitions, choosing optimal strategy for each join.data in the partitions resides on the foreign server. More advantageous, if the partitions themselves are foreign tables, i.e. This is advantageous when the size of join result is significantly smaller than the result of cross product. With partition-wise join, the join between matching partitions is performed and the results are appended. Without partition-wise join, the join will be performed after "appending" all the rows from each partition of either partitioned table. ![]() There exists an equi-join condition t1.a = t2.b which includes partition keys from both the tables.> Index Scan using prt2_p2_b on prt2_p2 t2_1 Select * from prt1 t1, prt2 t2 where t1.a = t2.b and t1.b = 0 and t2.b between 0 With partition-wise join the plan for the same query looks like: Select * from prt1 t1, prt2 t2 where t1.a = t2.b and t1.b = 0 and t2.b between 0 įilter: ((b >= 0) AND (b Index Scan using prt2_p2_b on prt2_p2 t2_1 Without partition-wise join, the plan for a join between these two tables looks like: Those three form the matching pairs of partitions. And all join partners for a row in prt1_p3 come from prt2_p3. All join partners for a row in prt1_p2 come from prt2_p2. Consider two tables partitioned as follows:ĬREATE TABLE prt1 (a int, b int, c varchar) PARTITION BY RANGE(a) ĬREATE TABLE prt1_p1 PARTITION OF prt1 FOR VALUES FROM (0) TO (5000) ĬREATE TABLE prt1_p2 PARTITION OF prt1 FOR VALUES FROM (5000) TO (15000) ĬREATE TABLE prt1_p3 PARTITION OF prt1 FOR VALUES FROM (15000) TO (30000) ĬREATE TABLE prt2 (a int, b int, c varchar) PARTITION BY RANGE(b) ĬREATE TABLE prt2_p1 PARTITION OF prt2 FOR VALUES FROM (0) TO (5000) ĬREATE TABLE prt2_p2 PARTITION OF prt2 FOR VALUES FROM (5000) TO (15000) ĬREATE TABLE prt2_p3 PARTITION OF prt2 FOR VALUES FROM (15000) TO (30000) Īll join partners for a row in prt1_p1 come from prt2_p1. This technique of breaking down a join between partition tables into joins between their partitions is called partition-wise join. Because of this, the join between partitioned tables can be broken down into joins between the matching partitions. The equi-join between partition keys implies that all the join partners for a given row in a given partition of one partitioned table must be in the corresponding partition of the other partitioned table. The first one that got committed was basic partition-wise join.Ī join between two similarly partitioned tables can be broken down into joins between their matching partitions if there exists an equi-join condition between the partition keys of the joining tables. PostgreSQL 11's query optimizer is gearing up to take advantage of this "no-inference" representation. Unlike inheritance-based partitioning, declarative partitioning introduced in PostgreSQL 10 leaves nothing to infer about how the data is divided into partitions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |