Hi all,
(Sorry, I know this is a repeat, but if you're using message threads,
the previous one was a reply to an OLD subject.)
The query below is fairly fast if the commented sub-select is
commented, but once I included that column, it takes over 10 minutes to
return results. Can someone shed some light on it? I was able to redo
the query using left joins instead, and it only marginally increased
result time. This is an application (Quasar by Linux Canada) I can't
change the query in, so want to see if there's a way to tune the
database for it to perform faster. Application developer says that
Sybase is able to run this same query with the price column included
with only marginal increase in time.
select item.item_id,item_plu.number,item.description,
(select dept.name from dept where dept.dept_id = item.dept_id)
-- ,(select price from item_price
-- where item_price.item_id = item.item_id
-- and item_price.zone_id = 'OUsEaRcAA3jQrg42WHUm8A'
-- and item_price.price_type = 0
-- and item_price.size_name = item.sell_size)
from item join item_plu on item.item_id = item_plu.item_id and
item_plu.seq_num = 0
where item.inactive_on is null and exists (select item_num.number from
item_num
where item_num.item_id = item.item_id)
and exists (select stocked from item_store where stocked = 'Y'
and item_store.item_id = item.item_id)
Explain analyze without price column:
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=1563.82..13922.00 rows=10659 width=102) (actual
time=165.988..386.737 rows=10669 loops=1)
Hash Cond: (item.item_id =
item_store.item_id)
-> Hash Join (cost=1164.70..2530.78 rows=10659 width=148) (actual
time=129.804..222.008 rows=10669 loops=1)
Hash Cond: (item.item_id =
item_plu.item_id)
-> Hash Join (cost=626.65..1792.86 rows=10661 width=93)
(actual time=92.930..149.267 rows=10669 loops=1)
Hash Cond: (item.item_id =
item_num.item_id)
-> Seq Scan on item (cost=0.00..882.67 rows=10665
width=70) (actual time=0.006..17.706 rows=10669 loops=1)
Filter: (inactive_on IS
NULL)
-> Hash (cost=493.39..493.39 rows=10661 width=23)
(actual time=92.872..92.872 rows=10672 loops=1)
-> HashAggregate (cost=386.78..493.39 rows=10661
width=23) (actual time=59.193..75.303 rows=10672 loops=1)
-> Seq Scan on item_num (cost=0.00..339.22
rows=19022 width=23) (actual time=0.007..26.013 rows=19040 loops=1)
-> Hash (cost=404.76..404.76 rows=10663 width=55) (actual
time=36.835..36.835 rows=10672 loops=1)
-> Seq Scan on item_plu (cost=0.00..404.76 rows=10663
width=55) (actual time=0.010..18.609 rows=10672 loops=1)
Filter: (seq_num =
0)
-> Hash (cost=265.56..265.56 rows=10685 width=23) (actual
time=36.123..36.123 rows=10672
loops=1)
-> Seq Scan on item_store (cost=0.00..265.56 rows=10685
width=23) (actual time=0.015..17.959 rows=10672 loops=1)
Filter: (stocked =
'Y'::bpchar)
SubPlan
1
-> Seq Scan on dept (cost=0.00..1.01 rows=1 width=32) (actual
time=0.002..0.004 rows=1 loops=10669)
Filter: (dept_id =
$0)
Total runtime: 401.560
ms
(21 rows)
Explain with price column:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=1563.82..4525876.70 rows=10659 width=106) (actual
time=171.186..20863.887 rows=10669 loops=1)
Hash Cond: (item.item_id = item_store.item_id)
-> Hash Join (cost=1164.70..2530.78 rows=10659 width=152) (actual
time=130.025..236.528 rows=10669 loops=1)
Hash Cond: (item.item_id = item_plu.item_id)
-> Hash Join (cost=626.65..1792.86 rows=10661 width=97)
(actual time=92.780..158.514 rows=10669 loops=1)
Hash Cond: (item.item_id = item_num.item_id)
-> Seq Scan on item (cost=0.00..882.67 rows=10665
width=74) (actual time=0.008..18.836 rows=10669 loops=1)
Filter: (inactive_on IS NULL)
-> Hash (cost=493.39..493.39 rows=10661 width=23)
(actual time=92.727..92.727 rows=10672 loops=1)
-> HashAggregate (cost=386.78..493.39 rows=10661
width=23) (actual time=59.064..75.243 rows=10672 loops=1)
-> Seq Scan on item_num (cost=0.00..339.22
rows=19022 width=23) (actual time=0.009..26.287 rows=19040 loops=1)
-> Hash (cost=404.76..404.76 rows=10663 width=55) (actual
time=37.206..37.206 rows=10672 loops=1)
-> Seq Scan on item_plu (cost=0.00..404.76 rows=10663
width=55) (actual time=0.011..18.823 rows=10672 loops=1)
Filter: (seq_num = 0)
-> Hash (cost=265.56..265.56 rows=10685 width=23) (actual
time=36.395..36.395 rows=10672 loops=1)
-> Seq Scan on item_store (cost=0.00..265.56 rows=10685
width=23) (actual time=0.015..18.120 rows=10672 loops=1)
Filter: (stocked = 'Y'::bpchar)
SubPlan 1
-> Seq Scan on dept (cost=0.00..1.01 rows=1 width=32) (actual
time=0.002..0.004 rows=1 loops=10669)
Filter: (dept_id = $0)
SubPlan 2
-> Seq Scan on item_price (cost=0.00..423.30 rows=1 width=8)
(actual time=1.914..1.914 rows=0 loops=10669)
Filter: ((item_id = $1) AND (zone_id =
'OUsEaRcAA3jQrg42WHUm8A'::bpchar) AND (price_type = 0) AND
((size_name)::text = ($2)::text))
Total runtime: 20879.388 ms
(24 rows)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
