new BUG: "postgresql 9.2.3: very long query time"

On Friday, February 22, 2013, Tom Lane wrote:

> Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> > The slowness was introduced with this:
> > Use parameterized paths to generate inner indexscans more flexibly.
>
> Try increasing from_collapse_limit to 11 or more.
>

I've increased it to 20 and still no luck.

>
> I'm not exactly sure why the param-path patch might have changed this
> behavior, but in any case the collapse limits ought to be the first
> thing you think of when you see a crappy plan for a many-relation query.
>

I've stripped it down to this:

explain (analyze,buffers)
select 1 as shift_date
from cb_order order1
inner join Template template2 on order1."template" =
template2."id"
left outer join Product product1 on template2."id" =
product1."id",
Template template1 cross join Product
product2
where order1."template" = template1."id"
and (
template2."id"=product2."id"
or
case when product1."id" is not null
then 1
when template2."id" is not null
then 0
end <>1
and
product2."id"=2916353
) ;

The good plan uses a BitmapOr on a product2.id index to satisfy "
template2.id=product2.id or product2.id= 2916353" (which then needs to use
a filter to check that the CASE part holds in case the true branch of the
OR was the branch with 2916353)

The bad plan seems to have forgotten how to do that, and so seq scans
product2 repeatedly. If I remove the CASE, then it uses the BitmapOr, so
what it has forgotten seems to be that A or (B and C) can only be true if
(A or C) is true.

I say "forgot", because the planner knows that the bad plan is way worse
than the good one, so it is probably about a lack-of-proof-of-correctness
rather than some small change in cost estimation pushing one over the other.

But it isn't as simple as that, as if I replace the CASE with one that
doesn't refer to product1.id, then it relearns how to use the BitmapOr.

case when random()<0.5 then 1
when template2."id" is not null then 0
end <>1

I'm not sure where to go from here.

Cheers,

Jeff