Re: or kills performance

Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
> The parenthesis are correct:
> where a.batchid=382 and e.stock>0 and e.ownerid=1 and
> (
> (d.leadfree and leadstateid in (1,3) )
> or
> (not d.leadfree and leadstateid in (2,3,4) )
> )

[ goes back and looks more closely ] The row count estimates in your
EXPLAINs are so far off that it's a wonder you got an OK plan for either
query. Have you ANALYZEd these tables recently?

The direct source of the problem seems to be that leadfree and
leadstateid come from different tables, so you're taking what had been
independent filter conditions for the two tables and converting them into
a join condition that can't be applied until after the join. However it
doesn't look to me like that is really a huge deal, because apparently
these conditions are pretty unselective and you'd be reading most of
each table anyway. What is really causing the problem is the choice to
join partsassembly last in the slow query; in the faster query, that's
joined before joining to assemblies and assembliesbatch, and apparently
that reduces the number of joins to assembliesbatch very considerably.

With the rowcount estimates so far off, though, it's really just luck
that the planner makes a good join order choice in either case. And it
doesn't look like the conditions are too hard for the planner to figure
out ... I think it must be working with obsolete statistics.

regards, tom lane