Horton

Re: Partition-wise join for join between (declaratively) partitioned tables

Robert Haas <robertmhaas@gmail.com>

View thread View on postgr.es
From: Robert Haas <robertmhaas@gmail.com>
To: Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>
Cc: Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2017-03-14T00:17:26Z
Lists: pgsql-hackers 
On Mon, Mar 13, 2017 at 3:24 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> Haven't looked at 0007 yet.

+               if (rel->part_scheme)
+               {
+                       int             cnt_parts;
+
+                       for (cnt_parts = 0; cnt_parts < nparts; cnt_parts++)
+                       {
+                               if (rel->part_oids[cnt_parts] ==
childRTE->relid)
+                               {
+                                       Assert(!rel->part_rels[cnt_parts]);
+                                       rel->part_rels[cnt_parts] = childrel;
+                               }
+                       }
+               }

It's not very appealing to use an O(n^2) algorithm here.  I wonder if
we could arrange things so that inheritance expansion expands
partitions in the right order, and then we could just match them up
one-to-one.  This would probably require an alternate version of
find_all_inheritors() that expand_inherited_rtentry() would call only
for partitioned tables.  Failing that, another idea would be to use
qsort() or qsort_arg() to put the partitions in the right order.

+       if (relation->rd_rel->relkind != RELKIND_PARTITIONED_TABLE ||
+               !inhparent ||
+               !(rel->part_scheme = find_partition_scheme(root, relation)))

Maybe just don't call this function in the first place in the
!inhparent case, instead of passing down an argument that must always
be true.

+               /* Match the partition key types. */
+               for (cnt_pks = 0; cnt_pks < partnatts; cnt_pks++)
+               {
+                       /*
+                        * For types, it suffices to match the type
id, mod and collation;
+                        * len, byval and align are depedent on the first two.
+                        */
+                       if (part_key->partopfamily[cnt_pks] !=
part_scheme->partopfamily[cnt_pks] ||
+                               part_key->partopcintype[cnt_pks] !=
part_scheme->partopcintype[cnt_pks] ||
+                               part_key->parttypid[cnt_pks] !=
part_scheme->key_types[cnt_pks] ||
+                               part_key->parttypmod[cnt_pks] !=
part_scheme->key_typmods[cnt_pks] ||
+                               part_key->parttypcoll[cnt_pks] !=
part_scheme->key_collations[cnt_pks])
+                               break;
+               }

I think memcmp() might be better than a for-loop.

Overall this one looks pretty good and straightforward.  Of course, I
haven't looked at the main act (0009) yet.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Commits

  1. Basic partition-wise join functionality.

  2. Assorted preparatory refactoring for partition-wise join.

  3. Teach adjust_appendrel_attrs(_multilevel) to do multiple translations.

  4. Stamp 10beta2.

  5. Eat XIDs more efficiently in recovery TAP test.

  6. Abstract logic to allow for multiple kinds of child rels.

  7. Implement SortSupport for macaddr data type

  8. Attempt to stabilize grouping sets regression test plans.

  9. Teach xlogreader to follow timeline switches

  10. Don't scan partitioned tables.

  11. Fix grammar.

  12. postgres_fdw: Push down FULL JOINs with restriction clauses.

  13. Some preliminary refactoring towards partitionwise join.

  14. contrib/amcheck needs RecentGlobalXmin to be PGDLLIMPORT'ified.

  15. Print test parameters like "foo: 123", and results like "foo = 123".