CODEWITHSUNDEEP
postgresqlpartitioning
Leandro Guimarães
Leandro Guimarães

Reputation: 59

PostgreSQL Partition range - strange behaviour

I'm partitioning a table (t1) using an integer range using the check like this:

CONSTRAINT t1_201611_check CHECK (date_id >= 20161101 AND date_id <= 20161130)
CONSTRAINT t1_201612_check CHECK (date_id >= 20161201 AND date_id <= 20161231)

so on..

It works fine and search in the right partitioned table when I execute the following query:

select * from t1
where date_id >= 20161201
and date_id <= 20161231;

But, when I execute the following query, for some reason it search the information in all partitioned tables:

select * from t1
where date_id >= to_char('2016-12-01'::date, 'YYYYMMDD')::int
and date_id <= to_char('2016-12-31'::date, 'YYYYMMDD')::int;

I think PostgreSQL does something wrong when it receives a function in the where clause, am I right?

How can I change this behavior?

Thanks!

Upvotes: 2

Views: 592

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

If you need computed parameters then build it dinamically:

create or replace function f(_start_date date, _end_date date)
returns setof t1 as $f$

begin
    return query execute $$ 
        select *
        from t1
        where date_id >= $1 and date_id <= $2
    $$ using to_char(_start_date, 'YYYYMMDD')::int, to_char(_end_date, 'YYYYMMDD')::int
    ;
end;

$f$ language plpgsql;

select *
from f('2016-12-01'::date, '2016-12-31'::date);

Upvotes: 1

IMSoP
IMSoP

Reputation: 97718

The ability of the system to limit to a specific partition is known as "constraint exclusion" (right now, Postgres doesn't have a built-in concept of "partitions", just the pieces you need to build them).

The important thing to understand is that this mechanism is built into the query planner. If you run EXPLAIN ... on your query, you will see nodes in the plan for scanning each child table (partition).

What "constraint exclusion" does is delete those nodes if the constraints on the child table mean it cannot possibly contain any relevant rows. This all happens before the query is executed.

Now, the problem with your second query is that the planner doesn't know what the value of to_char('2016-12-01'::date, 'YYYYMMDD')::int is - it hasn't run the function yet. (You and I know from looking at it that it's equivalent to the constant value 20161201, but that's a complex leap of logic for a compiler like Postgres's query planner!) So it can't prove that no rows will be returned from any particular table in order to exclude them.

At the bottom of the manual page on partitioning, there is this tip:

Keep the partitioning constraints simple, else the planner may not be able to prove that partitions don't need to be visited. Use simple equality conditions for list partitioning, or simple range tests for range partitioning, as illustrated in the preceding examples. A good rule of thumb is that partitioning constraints should contain only comparisons of the partitioning column(s) to constants using B-tree-indexable operators.

Upvotes: 1

Related Questions