when using functions on PostgreSQL partitoned tables it does a full table scan

Question

The tables are partitioned in a PostgreSQL 9 database. When I run the following script:

select * from node_channel_summary
where report_date between '2012-11-01' AND '2012-11-02';

it send the data from the proper tables without doing a full table scan. Yet if I run this script:

select * from node_channel_summary
where report_date between trunc(sysdate)-30 AND trunc(sysdate)-29;

in this case it does a full table scan which performance is unacceptable. The -30 and -29 will be replaced by parameters.

After doing some research, Postgres doesn't work properly with functions and partitioned tables.

Does somebody know as a work around to resolve this issue?

Answer 1

The issue is that PostgreSQL calculates and caches execution plans when you compile the function. This is a problem for partitioned tables because PostgreSQL uses the query planner to eliminate partitions. You can get around this by specifying your query as a string, forcing PostgreSQL to re-parse and re-plan your query at run time:

FOR row IN EXECUTE 'select * from node_channel_summary where report_date between trunc(sysdate)-30 AND trunc(sysdate)-29' LOOP
    -- ...
END LOOP;

-- or
RETURN QUERY EXECUTE 'select * from ...'