3

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?

jcolebrand
  • 15,889
  • 12
  • 75
  • 121
Bernard Brideau
  • 31
  • 1
  • are you using table inheritance to achieve partitioning? –  Dec 04 '12 at 14:59
  • 2
    What are `trunc()` and `sysdate`? They are certainly not PostgreSQL things... – dezso Dec 04 '12 at 15:45
  • I assumed we're actually talking about [EnterpriseDB](http://enterprisedb.com/solutions/oracle-compatibility-technology): fundamentally PostgreSQL, but supporting myriad Oracle features, including [`trunc()` and `sysdate`](http://www.enterprisedb.com/docs/en/9.1/oracompat/Postgres_Plus_Advanced_Server_Oracle_Compatibility_Guide-72.htm#P5531_309120). – willglynn Dec 04 '12 at 16:31
  • Proper tagging requires that you include only enterprisedb then, and not postgresql, so as not to confuse people as apparently has been done. Is there a reason I can't remove that tag? (aka I'm going to have done so, restore it ___and___ leave a comment if you must revert it) – jcolebrand Dec 04 '12 at 17:45
  • I am new to the PostgreSQL world. I am using the EntrepriseDB running SQL scripts from pgAdmin III. The function `trunc()` and `sysdate` are used in my SQL scripts and works as expected. Unless you have a better way of getting today's date in postgreSQL. – Bernard Brideau Dec 04 '12 at 18:19
  • The idiomatic way of getting today's date in PostgreSQL is `now()::date`. `trunc(sysdate)` is very Oracle. – willglynn Dec 05 '12 at 23:19
  • @willglynn personally I use `CURRENT_DATE` for readability's sake. – dezso Dec 06 '12 at 12:06

1 Answers1

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 ...'
willglynn
  • 11,210
  • 48
  • 40