Re: table partitioning and plpgsql functions in 8.2.3

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(at)berkeley(dot)edu]
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