Re: Declarative partitioning

Hi, Amit!

I tried to apply your patch. It still applies, but has some duplicate
oids. After fixing duplicate oids, I've noticed following warning during
compilation by clang-700.1.81.

scan.c:10308:23: warning: unused variable 'yyg' [-Wunused-variable]
struct yyguts_t * yyg = (struct yyguts_t*)yyscanner; /* This var may be
unused depending upon options. */
^
tablecmds.c:12922:6: warning: variable 'is_subpart' is used uninitialized
whenever 'if' condition is false [-Wsometimes-uninitialized]
if (parent != NULL)
^~~~~~~~~~~~~~
tablecmds.c:12931:12: note: uninitialized use occurs here
partKey = is_subpart ? list_nth(rootParent->rd_partkeys,
parent_level) :
^~~~~~~~~~
tablecmds.c:12922:2: note: remove the 'if' if its condition is always true
if (parent != NULL)
^~~~~~~~~~~~~~~~~~~
tablecmds.c:12912:19: note: initialize the variable 'is_subpart' to silence
this warning
bool is_subpart;
^
= '\0'
tablecmds.c:13375:37: warning: variable 'operoid' is uninitialized when
used here [-Wuninitialized]
comp_left_expr =
make_opclause(operoid, BOOLOID, false,

^~~~~~~
tablecmds.c:13326:17: note: initialize the variable 'operoid' to silence
this warning
Oid operoid;
^
= 0

Regression tests passed cleanly for me. I also examined code a bit. As I
get, for DML queries, declarative partitioning works like inheritance. It
just provides alternative way for collecting append_rel_list.
We're working on the other side of partitioning problem. Without solving
syntax problem, we're solving performance problems in pg_pathman extension:
https://github.com/postgrespro/pg_pathman. We already have interesting
results which you can see in blog posts [1], [2], [3].

We already have fast algorithm for partition selection in optimizer [1] and
effective optimization of filter conditions [3]. And we're planning to
implement following features:

- Execute time partitions selection (useful for nested loops and
prepared statements);
- Optimization of ordered output from patitioned tables (use Append
instead of MergeAppend when possible);
- Optimization of hash join when both tables are patitioned by join key.

9.6 feature freeze in coming, and we're planning our development resources
for 9.7. Besides providing an extension, we would like these features to
eventually arrive to core. In order to achieve this we need to port out
functionality over your declarative syntax. At first, we would need some
way for caching partition metadata suitable for fast partition selection.
For range partition it could be sorted array or RB-tree of partition
bounds. When we have this infrastructure, we can start porting pieces of
pg_pathman functionality to declarative partitiong.

So, our draft plan of patches would be following:

- Implement partition metadata cache suitable for fast partition
selection.
- Fast partition selection using metadata cache.
- Optimization of filter conditions passed to partitions.
- Execute time partitions selection (useful for nested loops and
prepared statements);
- Optimization of ordered output from patitioned tables (use Append
instead of MergeAppend when possible);
- Optimization of hash join when both tables are patitioned by join key.

I'd like to validate that this development plan doesn't overlaps with your
plans. If out plans are not overlapping then let's accept this plan of
work for 9.7.

Links.

[1] http://www.postgrespro.com/blog/pgsql/pg_pathman
[2] http://akorotkov.github.io/blog/2016/03/04/pg_pathman-beta-release/
[3]
http://akorotkov.github.io/blog/2016/03/14/pg_pathman-condition-processing/

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company