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
