Re: Declarative partitioning - another take

On 2016/09/02 14:38, Ashutosh Bapat wrote:
> Here's something I observed with your set of patches posted in June. I have
> not checked the latest set of patches. So, if it's something fixed, please
> ignore the mail and sorry for me being lazy.
> 
> prt1 is partitioned table and it shows following information with \d+
> 
> regression=# \d+ prt1
>                         Partitioned table "public.prt1"
>  Column |       Type        | Modifiers | Storage  | Stats target |
> Description
> --------+-------------------+-----------+----------+--------------+-------------
>  a      | integer           |           | plain    |              |
>  b      | integer           |           | plain    |              |
>  c      | character varying |           | extended |              |
> Partition Key: PARTITION BY RANGE (a)
> Indexes:
>     "iprt1_a" btree (a)
> 
> Shouldn't we show all the partitions of this table and may be their ranges
> of lists?

Something I thought about as well.  I will implement that.

> I found the partitions from EXPLAIN plan
> 
> regression=# explain verbose select * from prt1;
>                               QUERY PLAN
> -----------------------------------------------------------------------
>  Append  (cost=0.00..6.00 rows=301 width=13)
>    ->  Seq Scan on public.prt1  (cost=0.00..0.00 rows=1 width=40)
>          Output: prt1.a, prt1.b, prt1.c
>    ->  Seq Scan on public.prt1_p1  (cost=0.00..2.25 rows=125 width=13)
>          Output: prt1_p1.a, prt1_p1.b, prt1_p1.c
>    ->  Seq Scan on public.prt1_p3  (cost=0.00..1.50 rows=50 width=13)
>          Output: prt1_p3.a, prt1_p3.b, prt1_p3.c
>    ->  Seq Scan on public.prt1_p2  (cost=0.00..2.25 rows=125 width=13)
>          Output: prt1_p2.a, prt1_p2.b, prt1_p2.c
> (9 rows)
> 
> Then did \d+ on each of those to find their ranges

[ ... ]

> 
> As you will observe that the table prt1 can not have any row with a < 0 and
> a > 600. But when I execute
> 
> regression=# explain verbose select * from prt1 where a > 1000000;
>                             QUERY PLAN
> ------------------------------------------------------------------
>  Append  (cost=0.00..0.00 rows=1 width=40)
>    ->  Seq Scan on public.prt1  (cost=0.00..0.00 rows=1 width=40)
>          Output: prt1.a, prt1.b, prt1.c
>          Filter: (prt1.a > 1000000)
> (4 rows)
> 
> it correctly excluded all the partitions, but did not exclude the parent
> relation. I guess, we have enough information to exclude it. Probably, we
> should add a check constraint on the parent which is OR of the check
> constraints on all the partitions. So there are two problems here
> 
> 1. \d+ doesn't show partitions - this is probably reported earlier, I don't
> remember.

You just did, :)

As I said I will implement that on lines of how inheritance children are
listed (with additional information ie, range or list).

> 2. A combination of constraints on the partitions should be applicable to
> the parent. We aren't doing that.

How about on seeing that a RELOPT_OTHER_MEMBER_REL is partitioned parent
table, we can have get_relation_constraints() include a constant false
clause in the list of constraints returned for
relation_excluded_by_constraints() to process so that it is not included
in the append result by way of constraint exclusion.  One more option is
to mark such rels dummy in set_rel_size().

Thanks,
Amit