Repartitioned INSERT/SELECT improvements and new features

This is to track improvements and features that can be implemented upon https://github.com/citusdata/citus/pull/3376. I will clean-up and edit this issue, but for now, just pasting @marcocitus 's ideas from https://github.com/citusdata/citus/pull/3376#pullrequestreview-344457808 so we don't lose track of them:

Some ideas for future improvements:
- Replace read_intermediate_result function scan with a custom scan that returns tuples as they are parsed. That saves a lot of data copying, especially in large INSERT..SELECTs.
- Clean up intermediate results at the end of execution to avoid keeping and potentially flushing a lot of data on disk.
- Support GROUP BY distribution_column_of_target_table queries in the final INSERT..SELECT step

Some ideas for exciting new use cases for append-mostly data that look more feasible now:
- Do all your pre-processing in Citus. Simply shove your JSON objects into a distributed table with a bigserial as its distribution column and then transform it in parallel using INSERT..SELECT.
- Create a new distributed table type which has no distribution column and exactly one shard on each node (could use append) and supports metadata syncing. Writes are always done to the local shard. Allows you to write locally on any node, providing infinite scale and extremely high write-availability, and INSERT..SELECT is used to put data into more structured form.
- Create append-partitioned tables where each shard is a foreign table. Use INSERT..SELECT to put the data into a more structured form.
- Use intermediate result infrastructure for re-partition joins, allows combining subqueries and CTEs with re-partition joins.
- Change the distribution column (type) of your table.
- Keep N copies of your data with different distribution columns to do different types of joins.

Start with pull request #3376 and its review comment, then compare the listed ideas to the current repartitioned INSERT/SELECT behavior. The issue does not identify files, tests, or a single acceptance criterion; it needs to be split into a scoped task before implementation can begin.

Written by the indexing model from the issue text.