Apologies, I should have read more cafeully - this is already documented: 5.9.5 Caveats Constraint exclusion only works when the query's WHERE clause contains constants. A parameterized query will not be optimized, since the planner cannot know what partitions the parameter value might select at run time. For the same reason, "stable" functions such as CURRENT_DATE must be avoided. -----Original Message----- From: paul rivers [mailto:privers@xxxxxxxxxxxx] Sent: Sunday, April 08, 2007 2:40 PM To: 'pgsql general' Subject: table partitioning and plpgsql functions in 8.2.3 Overview: plpgsql functions seem to ignore partitioning, even with constraint_exclusion on. Description: Version is 8.2.3 on RHEL 4, constraint_exlusion is on. I have an events table (fw_events) partitioned by an int and a date (fw_id, fw_date for discussion) following the recommendations outlined in 5.9 of the manual. FWIW, each partition holds around 1M rows. There are presently about 250 partitions (2 ids, around 4+ months of dates). explain select count(*) from fw_events where fw_id = 1 and fw_date = '2007-04-08' shows that the single partition table is examined, and results are snappy when executed. I created a function to do the same count, and it took orders of magnitude longer. I then created a plpgsql function to return the explain plan instead, which seemed to indicate the plpgsql function scans all the partitions. Shouldn't the plpgsql function honor the partitioning and only examine the single partition in the above example? Thanks in advance, Paul
