Does it make sense for a simple or in a where clause to kill performance?
The statement with the OR takes 2500 ms and the statement without the OR
takes 190 ms:
select c.partid,c.pnid,c.leadstateid,e.stock from assembliesbatch a join
partsassembly b on a.assemblyid=b.assemblyid
join manufacturerpartpn c on c.partid=b.partid join assemblies d on
d.assemblyid=a.assemblyid
join stock e on e.pnid=c.pnid
where a.batchid=382 and e.stock>0 and e.ownerid=1 and
--THIS LINE CAUSING PROBLEM
((d.leadfree and leadstateid in (1,3)) or (not d.leadfree and leadstateid in
(2,3,4)))
order by partid,leadstateid
In this particular query, the first condition does not return any rows. When
I run it as
and not d.leadfree and leadstateid in (2,3,4)
then it takes only 190 ms and when I use only the first clause it takes less
then that and returns no rows.
Following are the 2 explain analyze results. (With Or first)
"Sort (cost=253.83..253.83 rows=1 width=16) (actual time=8368.145..8368.352
rows=62 loops=1)"
" Sort Key: c.partid, c.leadstateid"
" -> Nested Loop (cost=9.61..253.82 rows=1 width=16) (actual
time=14.358..8367.822 rows=62 loops=1)"
" -> Nested Loop (cost=9.61..243.77 rows=1 width=24) (actual
time=14.295..8353.241 rows=699 loops=1)"
" -> Nested Loop (cost=9.61..94.60 rows=16 width=20) (actual
time=0.100..3605.150 rows=291711 loops=1)"
" Join Filter: (((NOT "inner".leadfree) AND
(("outer".leadstateid = 2) OR ("outer".leadstateid = 3) OR
("outer".leadstateid = 4))) OR ("inner".leadfree AND (("outer".leadstateid =
1) OR ("outer".leadstateid = 3))))"
" -> Nested Loop (cost=0.00..44.14 rows=3 width=16)
(actual time=0.070..27.924 rows=793 loops=1)"
" -> Index Scan using ownerids on stock e
(cost=0.00..26.13 rows=3 width=8) (actual time=0.039..4.433 rows=793
loops=1)"
" Index Cond: (ownerid = 1)"
" Filter: (stock > 0)"
" -> Index Scan using manufacturerpartpn_pkey on
manufacturerpartpn c (cost=0.00..5.99 rows=1 width=12) (actual
time=0.010..0.015 rows=1 loops=793)"
" Index Cond: ("outer".pnid = c.pnid)"
" -> Materialize (cost=9.61..13.80 rows=419 width=5)
(actual time=0.003..1.444 rows=419 loops=793)"
" -> Seq Scan on assemblies d (cost=0.00..9.19
rows=419 width=5) (actual time=0.008..1.931 rows=419 loops=1)"
" -> Index Scan using ix_080c8ff0_5017_42a2_a174_28095b85106e_
on assembliesbatch a (cost=0.00..9.31 rows=1 width=4) (actual
time=0.009..0.009 rows=0 loops=291711)"
" Index Cond: ("outer".assemblyid = a.assemblyid)"
" Filter: (batchid = 382)"
" -> Index Scan using idx_u_assidpartid on partsassembly b
(cost=0.00..10.02 rows=2 width=8) (actual time=0.012..0.012 rows=0
loops=699)"
" Index Cond: (("outer".partid = b.partid) AND (b.assemblyid =
"outer".assemblyid))"
"Total runtime: 8368.708 ms"
(Without OR)
"Sort (cost=1251.95..1251.95 rows=1 width=16) (actual time=634.110..634.333
rows=62 loops=1)"
" Sort Key: c.partid, c.leadstateid"
" -> Nested Loop (cost=9.71..1251.94 rows=1 width=16) (actual
time=3.455..633.817 rows=62 loops=1)"
" -> Hash Join (cost=9.71..1168.03 rows=9 width=24) (actual
time=3.428..370.329 rows=16405 loops=1)"
" Hash Cond: ("outer".assemblyid = "inner".assemblyid)"
" -> Nested Loop (cost=0.00..1154.62 rows=368 width=20)
(actual time=0.080..235.833 rows=16472 loops=1)"
" -> Nested Loop (cost=0.00..44.16 rows=1 width=16)
(actual time=0.053..25.756 rows=699 loops=1)"
" -> Index Scan using ownerids on stock e
(cost=0.00..26.13 rows=3 width=8) (actual time=0.023..4.123 rows=793
loops=1)"
" Index Cond: (ownerid = 1)"
" Filter: (stock > 0)"
" -> Index Scan using manufacturerpartpn_pkey on
manufacturerpartpn c (cost=0.00..6.00 rows=1 width=12) (actual
time=0.010..0.013 rows=1 loops=793)"
" Index Cond: ("outer".pnid = c.pnid)"
" Filter: ((leadstateid = 2) OR (leadstateid
= 3) OR (leadstateid = 4))"
" -> Index Scan using partidpa on partsassembly b
(cost=0.00..1105.87 rows=368 width=8) (actual time=0.011..0.115 rows=24
loops=699)"
" Index Cond: ("outer".partid = b.partid)"
" -> Hash (cost=9.19..9.19 rows=210 width=4) (actual
time=3.324..3.324 rows=0 loops=1)"
" -> Seq Scan on assemblies d (cost=0.00..9.19 rows=210
width=4) (actual time=0.014..1.735 rows=417 loops=1)"
" Filter: (NOT leadfree)"
" -> Index Scan using ix_080c8ff0_5017_42a2_a174_28095b85106e_ on
assembliesbatch a (cost=0.00..9.31 rows=1 width=4) (actual
time=0.009..0.009 rows=0 loops=16405)"
" Index Cond: ("outer".assemblyid = a.assemblyid)"
" Filter: (batchid = 382)"
"Total runtime: 634.672 ms"
Thank You
Sim
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
