Re: or kills performance

My goal is a specific batchid, stock>0, ownerid=1 and either leadfree with a state of 1 or 3 OR not leadfree with a state of 2,3 or 4

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) )
)
order by partid,leadstateid

I checked my indices and every field in both the where clause and the join is already indexed.

I tried unioning the 2 queries and it was much faster then with the OR statement. (Took 200 ms as opposed to 2000 ms). The union will work, but it seems like overkill for a simple or clause. Is this the recommended way to do it?

select c.partid,c.pnid,c.leadstateid,e.stock from 
assemblies d join assembliesbatch a on d.assemblyid=a.assemblyid
join partsassembly b on b.assemblyid=d.assemblyid
join manufacturerpartpn c on c.partid=b.partid
join stock e on e.pnid=c.pnid
where a.batchid=382 and e.stock>0 and e.ownerid=1 and 
not d.leadfree and leadstateid in (2,3,4)
union 
select c.partid,c.pnid,c.leadstateid,e.stock from 
assemblies d join assembliesbatch a on d.assemblyid=a.assemblyid
join partsassembly b on b.assemblyid=d.assemblyid
join manufacturerpartpn c on c.partid=b.partid
join stock e on e.pnid=c.pnid
where a.batchid=382 and e.stock>0 and e.ownerid=1 and 
d.leadfree and leadstateid in (1,3)
order by partid,leadstateid


________________________________________________________________________________

"Sim Zacks"  writes:
> Does it make sense for a simple or in a where clause to kill performance?

Did you get the parenthesization correct?  Remember that AND binds more
tightly than OR.

                        regards, tom lane