Search Postgresql Archives

Re: Converting inherited partitions into declarative ones

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Fri, Sep 29, 2017 at 1:32 PM, Victor Yegorov <vyegorov@xxxxxxxxx> 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" ?


--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux