Re: [GENERAL] Converting inherited partitions into declarative ones - Mailing list pgsql-general

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