planner not choosing fastest estimated plan

I have a weird case where the planner doesn't choose the plan that it
itself believes to be the fastest plan.  If I disable seqscan, it then
chooses a faster plan (faster in estimate and faster in reality) than
the one chosen when all options were open to it.  I can't figure out
how this can be anything other than a bug.  The *collapse_limit
parameters are not restraining things.

I've created a dummy self-contained test case that is a simple
self-join of a partitioned table, with a function-based index.

If I analyze the tables after the function-based indexes are in place,
then the problems goes away.  And that is the production solution.
But still, a bug is a bug, even if there is a work around.

This is using the default configuration with LANG=C in 9.2, 9.3, and 9.4.

It was introduced in commit 5b7b5518d0ea56c422a19787, "Revise
parameterized-path mechanism to fix assorted issues"

I've tried compiling under OPTIMIZER_DEBUG, but the output did not
mean anything to me.  It looks like the only RELOPTINFO corresponding
to the join, "RELOPTINFO (1 2)" only lists the HashJoin when
enable_seqscan=on, and only contains NestLoop when enable_seqscan=off.I don't know why it doesn't list both in both
casesand then choose
 
the faster.



create table foo1 as select lpad(g::text,7,'0') as val1, g as num1
from generate_series(1,100000) g;
create table foo2 as select lpad(g::text,7,'0') as val1, g as num1
from generate_series(100001,1000000) g;
alter table foo2 inherit foo1;
create index on foo1(num1 );
create index on foo2(num1 );
analyze foo1; analyze foo2;
create language plperl;
CREATE OR REPLACE FUNCTION perlupper(text)RETURNS textLANGUAGE plperlIMMUTABLE COST 1000
AS $function$ return uc($_[0]);
$function$;
create index on foo1 (perlupper(val1));
create index on foo2 (perlupper(val1));


jjanes=# explain select a.num1 from foo1 a, foo1 b where
perlupper(a.val1)=perlupper(b.val1) and b.num1 = 987845;                                      QUERY PLAN
----------------------------------------------------------------------------------------Hash Join
(cost=32789.00..538040.65rows=10000 width=4)  Hash Cond: (perlupper(b.val1) = perlupper(a.val1))  ->  Append
(cost=0.00..16.64rows=2 width=8)        ->  Index Scan using foo1_num1_idx on foo1 b
 
(cost=0.00..8.28 rows=1 width=8)              Index Cond: (num1 = 987845)        ->  Index Scan using foo2_num1_idx on
foo2b
 
(cost=0.00..8.37 rows=1 width=8)              Index Cond: (num1 = 987845)  ->  Hash  (cost=15406.00..15406.00
rows=1000000width=12)        ->  Append  (cost=0.00..15406.00 rows=1000000 width=12)              ->  Seq Scan on foo1
a (cost=0.00..1541.00 rows=100000 width=12)              ->  Seq Scan on foo2 a  (cost=0.00..13865.00
 
rows=900000 width=12)

jjanes=# set enable_seqscan TO off;
jjanes=# explain select a.num1 from foo1 a, foo1 b where
perlupper(a.val1)=perlupper(b.val1) and b.num1 = 987845;                                          QUERY PLAN
-------------------------------------------------------------------------------------------------Nested Loop
(cost=17.14..60438.19rows=10000 width=4)  ->  Append  (cost=0.00..16.64 rows=2 width=8)        ->  Index Scan using
foo1_num1_idxon foo1 b
 
(cost=0.00..8.28 rows=1 width=8)              Index Cond: (num1 = 987845)        ->  Index Scan using foo2_num1_idx on
foo2b
 
(cost=0.00..8.37 rows=1 width=8)              Index Cond: (num1 = 987845)  ->  Append  (cost=17.14..30160.77 rows=5000
width=12)       ->  Bitmap Heap Scan on foo1 a  (cost=17.14..3022.65 rows=500 width=12)              Recheck Cond:
(perlupper(val1)= perlupper(b.val1))              ->  Bitmap Index Scan on foo1_perlupper_idx
 
(cost=0.00..17.01 rows=500 width=0)                    Index Cond: (perlupper(val1) = perlupper(b.val1))        ->
BitmapHeap Scan on foo2 a  (cost=92.22..27138.12
 
rows=4500 width=12)              Recheck Cond: (perlupper(val1) = perlupper(b.val1))              ->  Bitmap Index Scan
onfoo2_perlupper_idx
 
(cost=0.00..91.10 rows=4500 width=0)                    Index Cond: (perlupper(val1) = perlupper(b.val1))

Cheers,

Jeff