PostgreSQL partitioning with joined table - partition constraint not used in query plan

Question

I have a large table in PostgreSQL 9.2 that I've partitioned as described in the manual. Well... almost! My real partition key is not in the partitioned table itself, but in a joined table, like this (simplified): ``` -- millions to tens of millions of rows CREATE TABLE data ( slice_id integer NOT NULL, point_id integer NOT NULL, -- ... data columns ..., CONSTRAINT pk_data PRIMARY KEY (slice_id, point_id), CONSTRAINT fk_data_slice FOREIGN KEY (slice_id) REFERENCES slice (id) CONSTRAINT fk_data_point FOREIGN KEY (point_id) REFERENCES point (id) ) -- hundreds to thousands of rows CREATE TABLE slice ( id serial NOT NULL, partition_date timestamp without time zone NOT NULL, other_date timestamp without time zone NOT NULL, int_key integer NOT NULL CONSTRAINT pk_slice PRIMARY KEY (id) ) -- about 40,000 rows CREATE TABLE point ( -- ... similar to "slice" ... ) ``` The table to be partitioned (`data`) contains rows for every combination of `point` and `slice`, each of which has a compound key…

Accepted Answer

The only way to achieve it is to make the query dynamic:

```
create function select_from_data (p_date date)
returns setof data as $function$

declare
    min_slice_id integer,
    max_slice_id integer;

begin
    select min(slice_id), max(slice_id)
    into min_slice_id, max_slice_id
    from slice
    where partition_date = p_date;

return query execute
    $dynamic$
        select *
        from data
        where slice_id between $1 and $2
    $dynamic$
    using min_slice_id, max_slice_id;

end;
$function$ language plpgsql;
```

This will build the query with the appropriate slice range for the given date and will plan it at run time when the planner will have the information it needs to check for the exact partitions.

To make the function more generic without loosing the planner's ability to get information at running time use the `or parameter is null` construct in the filter.

```
create function select_from_data (
    p_date date,
    value_1 integer default null,
    value_2 integer default null
)
returns setof data as $function$

declare
    min_slice_id integer,
    max_slice_id integer;

begin
    select min(slice_id), max(slice_id)
    into min_slice_id, max_slice_id
    from slice
    where partition_date = p_date;

return query execute
    $dynamic$
        select *
        from data
        where
            slice_id between $1 and $2
            and (some_col = $3 or $3 is null)
            and (another_col = $4 or $4 is null)
    $dynamic$
    using min_slice_id, max_slice_id, value_1, value_2;

end;
$function$ language plpgsql;
```

Now if some parameter was passed as `null` it will not interfere with the query.

PostgreSQL partitioning with joined table - partition constraint not used in query plan

Solution

Problem