Postgres Pro
Downloads
Home   >   mailing lists

Thread: Constraint exclusion for timestamp expression

Constraint exclusion for timestamp expression

From
Victor Yegorov
Date:
Greetings.

I have a partitioned table, that can be reproduced the following way:

CREATE TABLE ara (
    ara_id      int4,
    name        varchar(11),
    run_id      int4,
    set_id      int4,
    created_at  timestamp,
    CONSTRAINT p_ara PRIMARY KEY (ara_id)
);
DO $partition$
DECLARE
    _tab    text;
    _mon    timestamp;
BEGIN
    FOR _mon IN
        SELECT * FROM generate_series('2013-10-01'::timestamp, date_trunc('mon',now()+interval'2mon'),interval'1mon') s(dt)
    LOOP
        _tab:='ara_'||to_char(_mon, 'YYYYMM');
        RAISE NOTICE '..oO( Creating % )', _tab;
        EXECUTE format($$CREATE TABLE %I (CONSTRAINT c_ara_partition CHECK (created_at >= %L AND created_at < %L)) INHERITS (ara)$$,
                             _tab, _mon, (_mon+interval'1mon'));
        EXECUTE format($$CREATE UNIQUE INDEX p_%s ON %I(ara_id)$$, _tab, _tab);
        EXECUTE format($$CREATE INDEX i_%s_name ON %I(name)$$, _tab, _tab);
        EXECUTE format($$CREATE INDEX i_%s_run_and_name ON %I(run_id,name)$$, _tab, _tab);
    END LOOP;

END;
$partition$;


Now, if I check plan for this query:

EXPLAIN SELECT * FROM ara
 WHERE ara.created_at
       BETWEEN (current_timestamp-interval'90 days')::timestamp
           AND (current_timestamp)::timestamp;

I can see that all partitions are considered by the planner.

If I replace the interval expression with constant, like this:

EXPLAIN SELECT * FROM ara
 WHERE ara.created_at
       BETWEEN '2014-02-20'::timestamp
           AND (current_timestamp)::timestamp;

then partition pruning kicks in and skips outdated partitions. The same happens for the future-dated partitions if I use a constant timestamp for the upper limit.

`constraint_exclusion` is default: partition

This happens on 9.1.13, but I get the same plans also on 9.3.4.


How can I enforce pruning to kick in for the initial expressions?



--
Victor Y. Yegorov

Re: Constraint exclusion for timestamp expression

From
Tom Lane
Date:
Victor Yegorov <vyegorov@gmail.com> writes:
> How can I enforce pruning to kick in for the initial expressions?

You already found out: use constants.  The planner can't remove
partitions on the basis of clauses involving volatile, or even
stable, functions, because their results might be different at
runtime.

            regards, tom lane

Re: Constraint exclusion for timestamp expression

From
Victor Yegorov
Date:
2014-05-20 14:26 GMT+03:00 Tom Lane <tgl@sss.pgh.pa.us>:
Victor Yegorov <vyegorov@gmail.com> writes:
> How can I enforce pruning to kick in for the initial expressions?

You already found out: use constants.  The planner can't remove
partitions on the basis of clauses involving volatile, or even
stable, functions, because their results might be different at
runtime.

Do you mean constants are the only way here?
I.e. there is absolutely no way to use any of the "current date / timestamp" functions available?


I've been trying to wrap current expressions into immutable functions, like `date_trunc`, without any luck.


--
Victor Y. Yegorov