Constraint exclusion for timestamp expression

From: Victor Yegorov <vyegorov(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Constraint exclusion for timestamp expression
Date: 2014-05-20 11:14:54
Message-ID: CAGnEbogT0uKeksQrgYjTSHZvh6+egAXkyy_sUrMJeEmUebzYEQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2014-05-20 11:26:55 Re: Constraint exclusion for timestamp expression
Previous Message Khangelani Gama 2014-05-20 10:39:05 Re: postgres 9.2.4 - ERROR: invalid input syntax for type numeric: ""