CODEWITHSUNDEEP
postgresqlconstraintspartitioning
marcinx
marcinx

Reputation: 165

PostgreSQL Partitioning. Constraint Exclusion not kicking in on SELECT

My partitioned table SELECT queries include all partitioned tables even though checks are in place and constraint_exclusion = on.

The insert trigger works fine and new rows are inserted into the correct tables. The SELECT however runs over all tables regardless of my WHERE clause.

Here is my config:

constraint_exclusion = on (both in postgresql.conf and also tried with "ALTER DATABASE bigtable SET constraint_exclusion=on;")

Master Table:

CREATE TABLE bigtable (
    id bigserial NOT NULL,
    userid integer NOT NULL,
    inserttime timestamp with time zone NOT NULL DEFAULT now()
)

Child Table 1:

CREATE TABLE bigtable_2013_11 (CHECK ( inserttime >= DATE '2013-11-01' AND inserttime < DATE '2013-12-01' )) INHERITS (bigtable);

Child Table 2:

CREATE TABLE bigtable_2013_12 (CHECK ( inserttime >= DATE '2013-12-01' AND inserttime < DATE '2014-01-01' )) INHERITS (bigtable);

Stored Procedure:

CREATE OR REPLACE FUNCTION bigtable_insert_function()
RETURNS TRIGGER AS $$
BEGIN

    IF ( NEW.inserttime >= DATE '2013-11-01' AND NEW.inserttime < DATE '2013-11-01' ) THEN
        INSERT INTO bigtable_2013_11 VALUES (NEW.*);
    ELSEIF (NEW.inserttime >= DATE '2013-12-01' AND NEW.inserttime < DATE '2014-01-01' ) THEN
        INSERT INTO bigtable_2013_12 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Bigtable insert date is out of range!';
    END IF;

    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER bigtable_insert_trigger BEFORE INSERT ON bigtable FOR EACH ROW EXECUTE PROCEDURE bigtable_insert_function();

It's pretty much the text book setup. The insert works fine:

INSERT INTO bigtable (userid, inserttime) VALUES ('1', now());

Above insert results in the new row being inserted correctly into 'bigtable_2013_11' only.

However I can't get the SELECT to exclude the irrelevant tables. All SELECTs always run over all tables. I would expect bigtable_2013_12 to be excluded when following SELECT queries are used:

SELECT * FROM bigtable WHERE inserttime >= DATE '2013-11-01'::date AND inserttime < '2013-12-01'::date;

SELECT * FROM bigtable WHERE EXTRACT(MONTH FROM inserttime) = 11 AND EXTRACT (YEAR FROM inserttime) = 2013;

However the result is always this:

"Result  (cost=0.00..68.90 rows=17 width=20)"
"  ->  Append  (cost=0.00..68.90 rows=17 width=20)"
"        ->  Seq Scan on bigtable  (cost=0.00..0.00 rows=1 width=20)"
"              Filter: ((inserttime >= '2013-11-02'::date) AND (inserttime < '2013-11-30'::date))"
"        ->  Seq Scan on bigtable_2013_11 bigtable  (cost=0.00..34.45 rows=8 width=20)"
"              Filter: ((inserttime >= '2013-11-02'::date) AND (inserttime < '2013-11-30'::date))"
"        ->  Seq Scan on bigtable_2013_12 bigtable  (cost=0.00..34.45 rows=8 width=20)"
"              Filter: ((inserttime >= '2013-11-02'::date) AND (inserttime < '2013-11-30'::date))"

Why are my checks not kicking in? I am out of ideas. Everything seems to be setup correctly. Did I miss anything? Any help would be greatly appreciated.

Upvotes: 3

Views: 2492

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78561

There are two things here. One is the seq scan. The other is the union all (i.e. append operator). The latter is completely unavoidable insofar as I'm aware. That is how table inheritance works -- or so it did last I used it. *However*, and this brings us to the former, you can add an index on that date field to eliminate the unneeded seq scans and bail early.

Or, possibly something related to date/timestamp conversions.

Upvotes: 3

Related Questions