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