Stored procedure slower than sql?

(Repost - did not appear to make it to the list the first time)

I have written a stored procedure for 8.1 that wraps a single (albeit complex) query, and uses 2 IN parameters (BIGINT,
INTEGER)in the FROM JOIN and WHERE clauses.  The procedure is written in SQL (as opposed to plpgsql - although testing
inplpgsql produces the same problem).  The results are SETOF a custom type (SMALLINT, NUMERIC(38,2), NUMERIC(38,2)). 
Thecentral query, when tested in psql and pgadmin III returns in 500 ms.  As a stored procedure, it returns in 22000
ms! How can a stored procedure containing a single query not implement the same execution plan (assumption based on the
dramaticperformance difference) that an identical ad-hoc query generates?  I ran a series of tests, and determined that
ifI replaced the parameters with hard-coded values, the execution time returned to 500ms.  Can anyone shed some light
onthis for me - it seems counter-intuitive? 

Here are some particulars about the underlying query and tables:

The query selects a month number from a generate_series(1,12) left outer joined on a subquery, which produces three
fields. The subquery is a UNION ALL of 5 tables.  Each of the five tables has 100 inherited partitions.  As you can see
fromthe execution plan, the partitioning constraint is successfully restricting the query to the appropriate partition
foreach of the five tables.  The constraint for each partition is a CHAR(2) field "partition_key"  = '00' (where '00'
isa two-digit CHAR(2) value that is returned from a function call ala table1.partition_key = partition_key($1) )  

Execution Plan :

Sort  (cost=10410.15..10410.65 rows=200 width=68) (actual time=273.050..273.071 rows=12 loops=1)
  Sort Key: mm.monthnumber
  ->  HashAggregate  (cost=10398.01..10402.51 rows=200 width=68) (actual time=272.970..273.001 rows=12 loops=1)
        ->  Hash Ltable5 Join  (cost=10370.01..10390.51 rows=1000 width=68) (actual time=272.817..272.902 rows=13
loops=1)
              Hash Cond: (("outer".monthnumber)::double precision = "inner".monthnumber)
              ->  Function Scan on generate_series mm  (cost=0.00..12.50 rows=1000 width=4) (actual time=0.018..0.043
rows=12loops=1) 
              ->  Hash  (cost=10369.99..10369.99 rows=10 width=72) (actual time=272.769..272.769 rows=8 loops=1)
                    ->  Append  (cost=1392.08..10369.89 rows=10 width=47) (actual time=39.581..272.734 rows=8 loops=1)
                          ->  Subquery Scan "*SELECT* 1"  (cost=1392.08..1392.15 rows=2 width=47) (actual
time=39.576..39.582rows=1 loops=1) 
                                ->  HashAggregate  (cost=1392.08..1392.13 rows=2 width=47) (actual time=39.571..39.573
rows=1loops=1) 
                                      ->  Result  (cost=0.00..1392.05 rows=2 width=47) (actual time=25.240..39.538
rows=1loops=1) 
                                            ->  Append  (cost=0.00..1392.03 rows=2 width=47) (actual
time=25.224..39.518rows=1 loops=1) 
                                                  ->  Seq Scan on table1 table1  (cost=0.00..14.50 rows=1 width=47)
(actualtime=0.003..0.003 rows=0 loops=1) 
                                                        Filter: ((partition_key = '12'::bpchar) AND
(substr((indexed_field)::text,2, 1) = '5'::text) AND (table_key = 10265512) AND (date_part('year'::text,
(event_date)::timestampwithout time zone) = 2005::double precision)) 
                                                  ->  Seq Scan on table1_p12 table1  (cost=0.00..1377.53 rows=1
width=28)(actual time=25.214..39.503 rows=1 loops=1) 
                                                        Filter: ((partition_key = '12'::bpchar) AND
(substr((indexed_field)::text,2, 1) = '5'::text) AND (table_key = 10265512) AND (date_part('year'::text,
(event_date)::timestampwithout time zone) = 2005::double precision)) 
                          ->  Subquery Scan "*SELECT* 2"  (cost=2741.47..2741.56 rows=2 width=47) (actual
time=78.140..78.140rows=0 loops=1) 
                                ->  HashAggregate  (cost=2741.47..2741.54 rows=2 width=47) (actual time=78.134..78.134
rows=0loops=1) 
                                      ->  Result  (cost=0.00..2741.45 rows=2 width=47) (actual time=78.128..78.128
rows=0loops=1) 
                                            ->  Append  (cost=0.00..2741.43 rows=2 width=47) (actual
time=78.122..78.122rows=0 loops=1) 
                                                  ->  Seq Scan on table2 table2  (cost=0.00..12.40 rows=1 width=47)
(actualtime=0.004..0.004 rows=0 loops=1) 
                                                        Filter: ((partition_key = '12'::bpchar) AND
(substr((indexed_field)::text,2, 1) = '5'::text) AND (table_key = 10265512) AND (date_part('year'::text,
(event_date)::timestampwithout time zone) = 2005::double precision)) 
                                                  ->  Seq Scan on table2_p12 table2  (cost=0.00..2729.03 rows=1
width=29)(actual time=78.109..78.109 rows=0 loops=1) 
                                                        Filter: ((partition_key = '12'::bpchar) AND
(substr((indexed_field)::text,2, 1) = '5'::text) AND (table_key = 10265512) AND (date_part('year'::text,
(event_date)::timestampwithout time zone) = 2005::double precision)) 
                          ->  Subquery Scan "*SELECT* 3"  (cost=3173.33..3173.41 rows=2 width=47) (actual
time=91.609..91.609rows=0 loops=1) 
                                ->  HashAggregate  (cost=3173.33..3173.39 rows=2 width=47) (actual time=91.603..91.603
rows=0loops=1) 
                                      ->  Result  (cost=0.00..3173.30 rows=2 width=47) (actual time=91.598..91.598
rows=0loops=1) 
                                            ->  Append  (cost=0.00..3173.28 rows=2 width=47) (actual
time=91.592..91.592rows=0 loops=1) 
                                                  ->  Seq Scan on table3 table3  (cost=0.00..10.90 rows=1 width=47)
(actualtime=0.003..0.003 rows=0 loops=1) 
                                                        Filter: ((partition_key = '12'::bpchar) AND
(substr((indexed_field)::text,2, 1) = '5'::text) AND (table_key = 10265512) AND (date_part('year'::text,
(event_date)::timestampwithout time zone) = 2005::double precision)) 
                                                  ->  Seq Scan on table3_p12 table3  (cost=0.00..3162.38 rows=1
width=29)(actual time=91.581..91.581 rows=0 loops=1) 
                                                        Filter: ((partition_key = '12'::bpchar) AND
(substr((indexed_field)::text,2, 1) = '5'::text) AND (table_key = 10265512) AND (date_part('year'::text,
(event_date)::timestampwithout time zone) = 2005::double precision)) 
                          ->  Subquery Scan "*SELECT* 4"  (cost=961.72..961.80 rows=2 width=29) (actual
time=11.647..11.694rows=7 loops=1) 
                                ->  HashAggregate  (cost=961.72..961.78 rows=2 width=29) (actual time=11.640..11.659
rows=7loops=1) 
                                      ->  Result  (cost=0.00..961.69 rows=2 width=29) (actual time=7.537..11.567
rows=10loops=1) 
                                            ->  Append  (cost=0.00..961.67 rows=2 width=29) (actual time=7.520..11.499
rows=10loops=1) 
                                                  ->  Seq Scan on table4 table4  (cost=0.00..22.30 rows=1 width=29)
(actualtime=0.003..0.003 rows=0 loops=1) 
                                                        Filter: ((partition_key = '12'::bpchar) AND
(substr((indexed_field)::text,2, 1) = '5'::text) AND (table_key = 10265512) AND (date_part('year'::text,
(event_date)::timestampwithout time zone) = 2005::double precision)) 
                                                  ->  Index Scan using table4_p12_recency_date_type_xyz_idx on
table4_p12table4  (cost=0.00..939.37 rows=1 width=29) (actual time=7.510..11.452 rows=10 loops=1) 
                                                        Index Cond: (table_key = 10265512)
                                                        Filter: ((partition_key = '12'::bpchar) AND
(substr((indexed_field)::text,2, 1) = '5'::text) AND (date_part('year'::text, (event_date)::timestamp without time
zone)= 2005::double precision)) 
                          ->  Subquery Scan "*SELECT* 5"  (cost=2100.89..2100.98 rows=2 width=47) (actual
time=51.658..51.658rows=0 loops=1) 
                                ->  HashAggregate  (cost=2100.89..2100.96 rows=2 width=47) (actual time=51.652..51.652
rows=0loops=1) 
                                      ->  Result  (cost=0.00..2100.87 rows=2 width=47) (actual time=51.646..51.646
rows=0loops=1) 
                                            ->  Append  (cost=0.00..2100.85 rows=2 width=47) (actual
time=51.641..51.641rows=0 loops=1) 
                                                  ->  Seq Scan on table5 table5  (cost=0.00..10.90 rows=1 width=47)
(actualtime=0.004..0.004 rows=0 loops=1) 
                                                        Filter: ((partition_key = '12'::bpchar) AND
(substr((indexed_field)::text,2, 1) = '5'::text) AND (table_key = 10265512) AND (date_part('year'::text,
(event_date)::timestampwithout time zone) = 2005::double precision)) 
                                                  ->  Seq Scan on table5_p12 table5  (cost=0.00..2089.95 rows=1
width=29)(actual time=51.627..51.627 rows=0 loops=1) 
                                                        Filter: ((partition_key = '12'::bpchar) AND
(substr((indexed_field)::text,2, 1) = '5'::text) AND (table_key = 10265512) AND (date_part('year'::text,
(event_date)::timestampwithout time zone) = 2005::double precision)) 
Total runtime: 274.605 ms


I appreciate your thoughts - this is a real mind-bender!

Matthew Peters