Re: Converting inherited partitions into declarative ones

On Fri, Sep 29, 2017 at 1:32 PM, Victor Yegorov <vyegorov(at)gmail(dot)com> wrote:

> Greetings.
>
> I am looking into new partitioning of 10rc1 on a copy of a production
> system.
> And I'm having tough times with the full scan.
>
> Per documentation:
> > It is possible to avoid this scan by adding a valid CHECK constraint to
> the table
> > that would allow only the rows satisfying the desired partition
> constraint before
> > running this command. It will be determined using such a constraint that
> the table
> > need not be scanned to validate the partition constraint.
>
>
> So I have this table with CHECK constraint:
>
> test=# \d stats_201503
> Table "public.stats_201503"
> Column Type Collation
> Nullable Default
> ---------------------------- --------------------------- ---------
> -------- ------------------------------------------------
> …
> created_at timestamp without time zone
> …
> Check constraints:
> "stats_201503_created_at_check" CHECK (created_at >= '2015-02-28
> 19:00:00'::timestamp without time zone AND created_at < '2015-03-31
> 20:00:00'::timestamp without time zone)
>
>
>
> Still, if I try to attach it, I get Full Scan:
>
> test=# ALTER TABLE jsm ATTACH PARTITION stats_201503 FOR VALUES FROM
> ('2015-02-28 19:00:00') TO ('2015-03-31 20:00:00');
> ALTER TABLE
> Time: 55502.875 ms (00:55.503)
>
>
>
> Is it possible to avoid Full Scan here? I have TBs worth of data in
> partitions,
> so it'll takes ages to switch to the declarative partitioning the way
> things stand now.
>
> Thanks in advance.
>
>
> --
> Victor Yegorov
>

*>Is it possible to avoid Full Scan here? *

*Have you verified that constraint_exclusion is set to "on" or "partition"
?*

*https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html
<https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html>*
* <https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html>*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.