Re: Declarative partitioning

Hi Alexander,

Thanks a lot for taking a look!

On Wed, Mar 16, 2016 at 10:56 PM, Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:
> I tried to apply your patch.  It still applies, but has some duplicate oids.

Actually, a reworked version I will post hopefully early next week
will have fixed this.

> 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

Oh, I will find and fix these issues if they are still left in the
newer version.  Thanks for reporting.

> 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.

Right, my latest patch doesn't touch this part at all.

> 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].

I have been following the blog posts.  Results look promising, :)

> 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.

I had to think about the internal metadata representation (and its
caching) when developing the tuple routing solution.  I am hopeful
that it is suitable for other executor mechanisms we will build for
partitioned tables.

> 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.

It looks OK to me.  Thanks for sharing it.

Thanks,
Amit