hi guys，
I met a query performance issue in postgresql 9.6.2 with multiple tables
joined.
there were 2 slow queries,and the reasons were the same:the optimizer
generate a bad explain which using nest loop.
attached is the query and its explain.all tables are small and the
indexes were only created on primary keys .
in query 1 I noticed the explain forecast the nest loop anti join
return 1 row as below,it was the result of (f join p) join pt:
-> Nested Loop (cost=1.95..14838.66 rows=1 width=163)
Join Filter: ((f.shop)::text = (s.uuid)::text)
-> Nested Loop Anti Join (cost=1.95..14743.60 rows=1
width=111)
-> Hash Join (cost=1.53..12067.46 rows=4751
width=115)
Hash Cond: ((p.shop)::text = (f.shop)::text)
-> Seq Scan on shopsku p
(cost=0.00..11483.96 rows=106892 width=106)
Filter: ((state)::text = 'normal'::text)
-> Hash (cost=1.29..1.29 rows=19 width=9)
-> Seq Scan on shopfranchise f
(cost=0.00..1.29 rows=19 width=9)
Filter: (enabled = 1)
-> Index Only Scan using platformsku_pkey on
platformsku pt (cost=0.42..0.55 rows=1 width=36)
Index Cond: (uuid = (p.platformsku)::text)
-> Seq Scan on shop s (cost=0.00..75.58 rows=1558
width=61)
-> Hash (cost=2823.76..2823.76 rows=43376 width=46)
-> Seq Scan on merchantsku m (cost=0.00..2823.76
rows=43376 width=46)
while in analyze explain,it actually returns 57458 row.so higher level
nest loop would get 57458*1558 rows,this cause this query runs for more
than 40 seconds.
-> Nested Loop (cost=1.95..14838.66 rows=1 width=163)
(actual time=0.817..43150.583 rows=57458 loops=1)
Join Filter: ((f.shop)::text = (s.uuid)::text)
Rows Removed by Join Filter: 89462106
-> Nested Loop Anti Join (cost=1.95..14743.60 rows=1
width=111) (actual time=0.060..408.092 rows=57458 loops=1)
-> Hash Join (cost=1.53..12067.46 rows=4751
width=115) (actual time=0.046..174.523 rows=57485 loops=1)
Hash Cond: ((p.shop)::text = (f.shop)::text)
-> Seq Scan on shopsku p
(cost=0.00..11483.96 rows=106892 width=106) (actual time=0.008..107.416
rows=106580 loops=1)
Filter: ((state)::text = 'normal'::text)
Rows Removed by Filter: 429
-> Hash (cost=1.29..1.29 rows=19 width=9)
(actual time=0.026..0.026 rows=20 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 9kB
-> Seq Scan on shopfranchise f
(cost=0.00..1.29 rows=19 width=9) (actual time=0.006..0.017 rows=20 loops=1)
Filter: (enabled = 1)
Rows Removed by Filter: 4
-> Index Only Scan using platformsku_pkey on
platformsku pt (cost=0.42..0.55 rows=1 width=36) (actual
time=0.003..0.003 rows=0 loops=57485)
Index Cond: (uuid = (p.platformsku)::text)
Heap Fetches: 0
-> Seq Scan on shop s (cost=0.00..75.58 rows=1558
width=61) (actual time=0.001..0.332 rows=1558 loops=57458)
If I disabled nest loop,ti only use 519 ms.
in query 2 ,the nest loop join also process more than 200m rows,it runs
almost 2 minutes.After disable nest loop,it use hash join,finished in
1.5 sec.
purchase join (shopfranchise f_4 join inventory k)
join gdname
-> Nested Loop Left Join (cost=3972.43..4192.40
rows=1 width=1074) (actual time=268.989..106066.932 rows=45615 loops=1)
Join Filter: (((k.shop)::text =
(purchase.shop)::text) AND ((k.shopsku)::text = (purchase.shopsku)::text))
Rows Removed by Join Filter: 208410367
(shopfranchise f_4 join inventory k) join
gdname
-> Hash Join (cost=3972.43..4165.52 rows=1
width=1112) (actual time=247.088..1754.448 rows=45615 loops=1)
Hash Cond: (((gdname.shop)::text =
(k.shop)::text) AND ((gdname.shopsku)::text = (k.shopsku)::text))
-> CTE Scan on gdname (cost=0.00..140.42
rows=7021 width=1246) (actual time=156.543..1563.121 rows=51153 loops=1)
-> Hash (cost=3925.81..3925.81 rows=3108
width=63) (actual time=90.520..90.520 rows=45622 loops=1)
Buckets: 65536 (originally 4096)
Batches: 1 (originally 1) Memory Usage: 4745kB
shopfranchise f_4 join inventory k
-> Hash Join (cost=1.53..3925.81
rows=3108 width=63) (actual time=0.046..70.173 rows=45622 loops=1)
Hash Cond: ((k.shop)::text =
(f_4.shop)::text)
-> Seq Scan on inventory k
(cost=0.00..3449.47 rows=88747 width=54) (actual time=0.009..22.978
rows=88747 loops=1)
-> Hash (cost=1.29..1.29
rows=19 width=9) (actual time=0.025..0.025 rows=19 loops=1)
Buckets: 1024 Batches:
1 Memory Usage: 9kB
-> Seq Scan on
shopfranchise f_4 (cost=0.00..1.29 rows=19 width=9) (actual
time=0.006..0.017 rows=19 loops=1)
Filter: (enabled = 1)
Rows Removed by
Filter: 4
-> CTE Scan on purchase (cost=0.00..15.36
rows=768 width=196) (actual time=0.001..1.013 rows=4569 loops=45615)
please kindly let me know there's any solution to solve the
problem,thanks a lot!
sql 1:
SELECT p.shop, p.uuid, s.name AS shopname, u.name AS shopowner, u.mobile AS ownermobile
, CASE WHEN p.barcode IS NULL THEN p.namegbk ELSE p.barcode END AS barcode, p.namegbk, 'å?¦' AS æ?¯å?¦ç»?è?¥, 'å?¦' AS å¹³å?°å??å??, p.costprice
, p.lastinprice
FROM dpos.shopfranchise f, dpos.shop s, dpos.shopsku p, dpos.user u
WHERE 1 = 1
AND (f.serviceprovider = '1000000'
OR '1000000' = '1000000')
AND f.enabled = '1'
AND f.shop = s.uuid
AND s.owner = u.uuid
AND f.shop = p.shop
AND NOT EXISTS (SELECT 1
FROM dpos.platformsku pt
WHERE p.platformsku = pt.uuid)
AND NOT EXISTS (SELECT 1
FROM dpos.merchantsku m
WHERE m.shop = s.uuid
AND m.shopsku = p.uuid)
AND p.state = 'normal';
explain 1:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=3476.62..18313.56 rows=1 width=221)
-> Hash Anti Join (cost=3476.35..18313.07 rows=1 width=154)
Hash Cond: (((s.uuid)::text = (m.shop)::text) AND ((p.uuid)::text = (m.shopsku)::text))
-> Nested Loop (cost=1.95..14838.66 rows=1 width=163)
Join Filter: ((f.shop)::text = (s.uuid)::text)
-> Nested Loop Anti Join (cost=1.95..14743.60 rows=1 width=111)
-> Hash Join (cost=1.53..12067.46 rows=4751 width=115)
Hash Cond: ((p.shop)::text = (f.shop)::text)
-> Seq Scan on shopsku p (cost=0.00..11483.96 rows=106892 width=106)
Filter: ((state)::text = 'normal'::text)
-> Hash (cost=1.29..1.29 rows=19 width=9)
-> Seq Scan on shopfranchise f (cost=0.00..1.29 rows=19 width=9)
Filter: (enabled = 1)
-> Index Only Scan using platformsku_pkey on platformsku pt (cost=0.42..0.55 rows=1 width=36)
Index Cond: (uuid = (p.platformsku)::text)
-> Seq Scan on shop s (cost=0.00..75.58 rows=1558 width=61)
-> Hash (cost=2823.76..2823.76 rows=43376 width=46)
-> Seq Scan on merchantsku m (cost=0.00..2823.76 rows=43376 width=46)
-> Index Scan using user_pkey on "user" u (cost=0.28..0.48 rows=1 width=57)
Index Cond: ((uuid)::text = (s.owner)::text)
(20 rows)
Time: 1.915 ms
analyze explan 1:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
Nested Loop (cost=3476.62..18313.56 rows=1 width=221) (actual time=47.228..43342.763 rows=12153 loops=1)
-> Hash Anti Join (cost=3476.35..18313.07 rows=1 width=154) (actual time=47.211..43275.569 rows=12153 loops=1)
Hash Cond: (((s.uuid)::text = (m.shop)::text) AND ((p.uuid)::text = (m.shopsku)::text))
-> Nested Loop (cost=1.95..14838.66 rows=1 width=163) (actual time=0.817..43150.583 rows=57458 loops=1)
Join Filter: ((f.shop)::text = (s.uuid)::text)
Rows Removed by Join Filter: 89462106
-> Nested Loop Anti Join (cost=1.95..14743.60 rows=1 width=111) (actual time=0.060..408.092 rows=57458 loops=1)
-> Hash Join (cost=1.53..12067.46 rows=4751 width=115) (actual time=0.046..174.523 rows=57485 loops=1)
Hash Cond: ((p.shop)::text = (f.shop)::text)
-> Seq Scan on shopsku p (cost=0.00..11483.96 rows=106892 width=106) (actual time=0.008..107.416 rows=106580 loops=1)
Filter: ((state)::text = 'normal'::text)
Rows Removed by Filter: 429
-> Hash (cost=1.29..1.29 rows=19 width=9) (actual time=0.026..0.026 rows=20 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on shopfranchise f (cost=0.00..1.29 rows=19 width=9) (actual time=0.006..0.017 rows=20 loops=1)
Filter: (enabled = 1)
Rows Removed by Filter: 4
-> Index Only Scan using platformsku_pkey on platformsku pt (cost=0.42..0.55 rows=1 width=36) (actual time=0.003..0.003 rows=0 loops=57485)
Index Cond: (uuid = (p.platformsku)::text)
Heap Fetches: 0
-> Seq Scan on shop s (cost=0.00..75.58 rows=1558 width=61) (actual time=0.001..0.332 rows=1558 loops=57458)
-> Hash (cost=2823.76..2823.76 rows=43376 width=46) (actual time=43.694..43.694 rows=48275 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 4190kB
-> Seq Scan on merchantsku m (cost=0.00..2823.76 rows=43376 width=46) (actual time=0.004..23.328 rows=48275 loops=1)
-> Index Scan using user_pkey on "user" u (cost=0.28..0.48 rows=1 width=57) (actual time=0.003..0.004 rows=1 loops=12153)
Index Cond: ((uuid)::text = (s.owner)::text)
Planning time: 1.160 ms
Execution time: 43345.917 ms
(28 rows)
Time: 43347.962 ms
query 2:
WITH
gdname (shop, shopsku, shopname, shopowner, ownermobile, barcode, barname, ismygd, isplatformgd, costprice, lastinprice)
AS
(
SELECT p.shop, p.uuid, s.name AS shopname, u.name AS shopowner, u.mobile AS ownermobile
, p.barcode, p.namegbk, 'æ?¯' AS æ?¯å?¦ç»?è?¥, 'æ?¯' AS å¹³å?°å??å??, p.costprice
, p.lastinprice
FROM dpos.shopfranchise f, dpos.shop s, dpos.shopsku p, dpos.merchantsku m, dpos.user u
WHERE 1 = 1
AND (f.serviceprovider = '1000000'
OR '1000000' = '1000000')
AND f.enabled = '1'
AND f.shop = s.uuid
AND s.owner = u.uuid
AND f.shop = p.shop
AND m.shop = p.shop
AND m.shopsku = p.uuid
AND p.state = 'normal'
UNION ALL
SELECT p.shop, p.uuid, s.name AS shopname, u.name AS shopowner, u.mobile AS ownermobile
, pt.barcode, pt.namegbk, 'å?¦' AS æ?¯å?¦ç»?è?¥, 'æ?¯' AS å¹³å?°å??å??, p.costprice
, p.lastinprice
FROM dpos.shopfranchise f, dpos.shop s, dpos.shopsku p, dpos.platformsku pt, dpos.user u
WHERE 1 = 1
AND (f.serviceprovider = '1000000'
OR '1000000' = '1000000')
AND f.enabled = '1'
AND f.shop = s.uuid
AND s.owner = u.uuid
AND f.shop = p.shop
AND p.platformsku = pt.uuid
AND (s.uuid,p.uuid)NOT in (SELECT m.shop,m.shopsku
FROM dpos.merchantsku m)
AND p.state = 'normal'
UNION ALL
SELECT p.shop, p.uuid, s.name AS shopname, u.name AS shopowner, u.mobile AS ownermobile
, CASE WHEN p.barcode IS NULL THEN p.namegbk ELSE p.barcode END AS barcode, p.namegbk, 'å?¦' AS æ?¯å?¦ç»?è?¥, 'å?¦' AS å¹³å?°å??å??, p.costprice
, p.lastinprice
FROM dpos.shopfranchise f, dpos.shop s, dpos.shopsku p, dpos.user u
WHERE 1 = 1
AND (f.serviceprovider = '1000000'
OR '1000000' = '1000000')
AND f.enabled = '1'
AND f.shop = s.uuid
AND s.owner = u.uuid
AND f.shop = p.shop
AND p.platformsku NOT IN (SELECT pt.uuid
FROM dpos.platformsku pt)
AND NOT EXISTS (SELECT 1
FROM dpos.merchantsku m
WHERE m.shop = s.uuid
AND m.shopsku = p.uuid)
AND p.state = 'normal'
),
purchase (shop, shopsku, price)
AS
(
SELECT p.shop, p.shopsku, CASE WHEN SUM(p.purchaseqty + p.purchaseorderqty) = 0 THEN 0 ELSE SUM(p.purchaseamount + p.purchaseorderamount) / SUM(p.purchaseqty + p.purchaseorderqty) END
FROM dpos.PurchaseSkuDay p, dpos.shopfranchise f, dpos.shop s
WHERE 1 = 1
AND (f.serviceprovider = '1000000'
OR '1000000' = '1000000')
AND f.shop = s.uuid
AND f.enabled = '1'
AND f.shop = p.shop
GROUP BY p.shop, p.shopsku
),
shopinv
AS
(
SELECT gdname.ismygd, gdname.isplatformgd, gdname.shopname, gdname.shopowner, gdname.ownermobile
, gdname.barname, gdname.barcode, SUM(k.qty) AS invqty, CASE WHEN SUM(k.qty) = 0 THEN MAX(CASE WHEN purchase.price IS NULL THEN gdname.costprice ELSE purchase.price END) ELSE round(
SUM(CASE WHEN purchase.price IS NULL THEN gdname.costprice ELSE purchase.price END * k.qty) / SUM(k.qty)) END AS invprc, SUM(CASE WHEN purchase.price IS NULL THEN gdname.costprice ELSE purchase.price END * k.qty) AS total
FROM dpos.Inventory k
INNER JOIN gdname ON k.shop = gdname.shop
AND k.shopsku = gdname.shopSku
LEFT JOIN purchase ON k.shop = purchase.shop
AND k.shopSku = purchase.shopSku
INNER JOIN dpos.shopfranchise f ON k.shop = f.shop
WHERE 1 = 1
AND (f.serviceprovider = '1000000'
OR '1000000' = '1000000')
AND f.enabled = '1'
GROUP BY gdname.ismygd, gdname.isplatformgd, gdname.shopname, gdname.shopowner, gdname.ownermobile, gdname.barname, gdname.barcode
),
lenthbarname
AS
(
SELECT t.barcode, MAX(t.barname) AS barname
FROM shopinv t
WHERE (t.barcode, char_length(barname)) IN (SELECT barcode, MAX(char_length(barname))
FROM shopinv
WHERE isplatformgd = 'å?¦'
GROUP BY barcode)
AND t.isplatformgd = 'å?¦'
GROUP BY t.barcode
)
SELECT shopinv.ismygd AS æ?¯å?¦ç»?è?¥, CASE WHEN lenthbarname.barname IS NULL THEN shopinv.barname ELSE lenthbarname.barname END AS å??å??, shopinv.barcode AS æ?¡ç ?, SUM(invqty) AS åº?å?æ?°é??, CASE WHEN SUM(invqty) = 0 THEN MAX(CASE WHEN shopinv.invprc = 0 THEN 0 ELSE shopinv.invprc END) ELSE round(
SUM(total) / SUM(invqty)) END AS åº?å?ä»·
, SUM(total) AS åº?å?é??é¢?
FROM shopinv
LEFT JOIN lenthbarname ON shopinv.barcode = lenthbarname.barcode
WHERE 1 = 1
GROUP BY shopinv.ismygd, shopinv.barcode, CASE WHEN lenthbarname.barname IS NULL THEN shopinv.barname ELSE lenthbarname.barname END;
analyze explain2:
HashAggregate (cost=70172.39..70172.41 rows=1 width=120) (actual time=112367.309..112377.370 rows=15071 loops=1)
Group Key: shopinv.ismygd, shopinv.barcode, CASE WHEN (lenthbarname.barname IS NULL) THEN (shopinv.barname)::text ELSE lenthbarname.barname END
CTE gdname
-> Append (cost=12131.47..65313.19 rows=7021 width=204) (actual time=156.539..1489.940 rows=51153 loops=1)
-> Nested Loop (cost=12131.47..15415.76 rows=62 width=201) (actual time=156.538..770.499 rows=45305 loops=1)
-> Nested Loop (cost=12131.19..15385.53 rows=62 width=154) (actual time=156.522..558.056 rows=45305 loops=1)
Join Filter: ((f.shop)::text = (s.uuid)::text)
-> Hash Join (cost=12130.91..15366.31 rows=62 width=120) (actual time=156.491..294.116 rows=45305 loops=1)
Hash Cond: (((m.shop)::text = (f.shop)::text) AND ((m.shopsku)::text = (p.uuid)::text))
-> Seq Scan on merchantsku m (cost=0.00..2872.73 rows=48273 width=46) (actual time=0.009..25.267 rows=48275 loops=1)
-> Hash (cost=12060.69..12060.69 rows=4681 width=111) (actual time=156.455..156.455 rows=51153 loops=1)
Buckets: 65536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 8066kB
-> Hash Join (cost=1.53..12060.69 rows=4681 width=111) (actual time=0.047..123.999 rows=51153 loops=1)
Hash Cond: ((p.shop)::text = (f.shop)::text)
-> Seq Scan on shopsku p (cost=0.00..11479.55 rows=106562 width=102) (actual time=0.005..79.855 rows=106575 loops=1)
Filter: ((state)::text = 'normal'::text)
Rows Removed by Filter: 429
-> Hash (cost=1.29..1.29 rows=19 width=9) (actual time=0.025..0.025 rows=19 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on shopfranchise f (cost=0.00..1.29 rows=19 width=9) (actual time=0.004..0.018 rows=19 loops=1)
Filter: (enabled = 1)
Rows Removed by Filter: 4
-> Index Scan using shop_pkey on shop s (cost=0.28..0.30 rows=1 width=61) (actual time=0.004..0.004 rows=1 loops=45305)
Index Cond: ((uuid)::text = (p.shop)::text)
-> Index Scan using user_pkey on "user" u (cost=0.28..0.48 rows=1 width=57) (actual time=0.003..0.003 rows=1 loops=45305)
Index Cond: ((uuid)::text = (s.owner)::text)
-> Nested Loop (cost=3691.80..20276.92 rows=4639 width=196) (actual time=65.433..226.829 rows=27 loops=1)
-> Hash Anti Join (cost=3691.38..15821.61 rows=4639 width=102) (actual time=44.905..210.683 rows=5848 loops=1)
Hash Cond: (((s_1.uuid)::text = (m_1.shop)::text) AND ((p_1.uuid)::text = (m_1.shopsku)::text))
-> Hash Join (cost=94.56..12153.73 rows=4681 width=111) (actual time=1.173..128.957 rows=51153 loops=1)
Hash Cond: ((p_1.shop)::text = (f_1.shop)::text)
-> Seq Scan on shopsku p_1 (cost=0.00..11479.55 rows=106562 width=67) (actual time=0.018..80.846 rows=106575 loops=1)
Filter: ((state)::text = 'normal'::text)
Rows Removed by Filter: 429
-> Hash (cost=94.32..94.32 rows=19 width=53) (actual time=1.124..1.124 rows=19 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Nested Loop (cost=1.80..94.32 rows=19 width=53) (actual time=0.093..1.109 rows=19 loops=1)
-> Hash Join (cost=1.53..85.06 rows=19 width=70) (actual time=0.080..0.998 rows=19 loops=1)
Hash Cond: ((s_1.uuid)::text = (f_1.shop)::text)
-> Seq Scan on shop s_1 (cost=0.00..75.56 rows=1556 width=61) (actual time=0.021..0.485 rows=1556 loops=1)
-> Hash (cost=1.29..1.29 rows=19 width=9) (actual time=0.031..0.031 rows=19 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on shopfranchise f_1 (cost=0.00..1.29 rows=19 width=9) (actual time=0.009..0.023 rows=19 loops=1)
Filter: (enabled = 1)
Rows Removed by Filter: 4
-> Index Scan using user_pkey on "user" u_1 (cost=0.28..0.48 rows=1 width=57) (actual time=0.004..0.005 rows=1 loops=19)
Index Cond: ((uuid)::text = (s_1.owner)::text)
-> Hash (cost=2872.73..2872.73 rows=48273 width=46) (actual time=43.628..43.628 rows=48275 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 4190kB
-> Seq Scan on merchantsku m_1 (cost=0.00..2872.73 rows=48273 width=46) (actual time=0.006..22.801 rows=48275 loops=1)
-> Index Scan using platformsku_pkey on platformsku pt_1 (cost=0.42..0.95 rows=1 width=71) (actual time=0.002..0.002 rows=0 loops=5848)
Index Cond: ((uuid)::text = (p_1.platformsku)::text)
-> Hash Anti Join (cost=17477.89..29550.31 rows=2320 width=221) (actual time=279.878..467.863 rows=5821 loops=1)
Hash Cond: (((s_2.uuid)::text = (m_2.shop)::text) AND ((p_2.uuid)::text = (m_2.shopsku)::text))
-> Hash Join (cost=13881.07..25917.94 rows=2341 width=146) (actual time=235.407..380.984 rows=51126 loops=1)
Hash Cond: ((p_2.shop)::text = (f_2.shop)::text)
-> Seq Scan on shopsku p_2 (cost=13786.51..25533.57 rows=53281 width=102) (actual time=234.252..333.740 rows=105892 loops=1)
Filter: ((NOT (hashed SubPlan 1)) AND ((state)::text = 'normal'::text))
Rows Removed by Filter: 1112
SubPlan 1
-> Seq Scan on platformsku pt (cost=0.00..13143.21 rows=257321 width=36) (actual time=0.006..94.254 rows=257316 loops=1)
-> Hash (cost=94.32..94.32 rows=19 width=53) (actual time=1.110..1.110 rows=19 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Nested Loop (cost=1.80..94.32 rows=19 width=53) (actual time=0.117..1.093 rows=19 loops=1)
-> Hash Join (cost=1.53..85.06 rows=19 width=70) (actual time=0.102..0.978 rows=19 loops=1)
Hash Cond: ((s_2.uuid)::text = (f_2.shop)::text)
-> Seq Scan on shop s_2 (cost=0.00..75.56 rows=1556 width=61) (actual time=0.024..0.450 rows=1556 loops=1)
-> Hash (cost=1.29..1.29 rows=19 width=9) (actual time=0.045..0.045 rows=19 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on shopfranchise f_2 (cost=0.00..1.29 rows=19 width=9) (actual time=0.008..0.020 rows=19 loops=1)
Filter: (enabled = 1)
Rows Removed by Filter: 4
-> Index Scan using user_pkey on "user" u_2 (cost=0.28..0.48 rows=1 width=57) (actual time=0.004..0.005 rows=1 loops=19)
Index Cond: ((uuid)::text = (s_2.owner)::text)
-> Hash (cost=2872.73..2872.73 rows=48273 width=46) (actual time=44.132..44.132 rows=48275 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 4190kB
-> Seq Scan on merchantsku m_2 (cost=0.00..2872.73 rows=48273 width=46) (actual time=0.007..22.933 rows=48275 loops=1)
CTE purchase
-> HashAggregate (cost=655.03..666.55 rows=768 width=54) (actual time=15.757..17.662 rows=4569 loops=1)
Group Key: p_3.shop, p_3.shopsku
-> Hash Join (cost=83.32..639.67 rows=768 width=78) (actual time=0.159..10.398 rows=7463 loops=1)
Hash Cond: ((p_3.shop)::text = (f_3.shop)::text)
-> Seq Scan on purchaseskuday p_3 (cost=0.00..484.78 rows=12778 width=78) (actual time=0.010..3.175 rows=12781 loops=1)
-> Hash (cost=83.08..83.08 rows=19 width=18) (actual time=0.133..0.133 rows=19 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Nested Loop (cost=0.28..83.08 rows=19 width=18) (actual time=0.031..0.124 rows=19 loops=1)
-> Seq Scan on shopfranchise f_3 (cost=0.00..1.29 rows=19 width=9) (actual time=0.007..0.018 rows=19 loops=1)
Filter: (enabled = 1)
Rows Removed by Filter: 4
-> Index Only Scan using shop_pkey on shop s_3 (cost=0.28..4.29 rows=1 width=9) (actual time=0.004..0.004 rows=1 loops=19)
Index Cond: (uuid = (f_3.shop)::text)
Heap Fetches: 10
CTE shopinv
-> GroupAggregate (cost=4192.41..4192.47 rows=1 width=1074) (actual time=106550.815..106620.312 rows=45529 loops=1)
Group Key: gdname.ismygd, gdname.isplatformgd, gdname.shopname, gdname.shopowner, gdname.ownermobile, gdname.barname, gdname.barcode
-> Sort (cost=4192.41..4192.41 rows=1 width=1074) (actual time=106550.789..106571.818 rows=45615 loops=1)
Sort Key: gdname.ismygd, gdname.isplatformgd, gdname.shopname, gdname.shopowner, gdname.ownermobile, gdname.barname, gdname.barcode
Sort Method: quicksort Memory: 11449kB
purchase join (shopfranchise f_4 join inventory k) join gdname
-> Nested Loop Left Join (cost=3972.43..4192.40 rows=1 width=1074) (actual time=268.989..106066.932 rows=45615 loops=1)
Join Filter: (((k.shop)::text = (purchase.shop)::text) AND ((k.shopsku)::text = (purchase.shopsku)::text))
Rows Removed by Join Filter: 208410367
(shopfranchise f_4 join inventory k) join gdname
-> Hash Join (cost=3972.43..4165.52 rows=1 width=1112) (actual time=247.088..1754.448 rows=45615 loops=1)
Hash Cond: (((gdname.shop)::text = (k.shop)::text) AND ((gdname.shopsku)::text = (k.shopsku)::text))
-> CTE Scan on gdname (cost=0.00..140.42 rows=7021 width=1246) (actual time=156.543..1563.121 rows=51153 loops=1)
-> Hash (cost=3925.81..3925.81 rows=3108 width=63) (actual time=90.520..90.520 rows=45622 loops=1)
Buckets: 65536 (originally 4096) Batches: 1 (originally 1) Memory Usage: 4745kB
shopfranchise f_4 join inventory k
-> Hash Join (cost=1.53..3925.81 rows=3108 width=63) (actual time=0.046..70.173 rows=45622 loops=1)
Hash Cond: ((k.shop)::text = (f_4.shop)::text)
-> Seq Scan on inventory k (cost=0.00..3449.47 rows=88747 width=54) (actual time=0.009..22.978 rows=88747 loops=1)
-> Hash (cost=1.29..1.29 rows=19 width=9) (actual time=0.025..0.025 rows=19 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on shopfranchise f_4 (cost=0.00..1.29 rows=19 width=9) (actual time=0.006..0.017 rows=19 loops=1)
Filter: (enabled = 1)
Rows Removed by Filter: 4
-> CTE Scan on purchase (cost=0.00..15.36 rows=768 width=196) (actual time=0.001..1.013 rows=4569 loops=45615)
CTE lenthbarname
-> HashAggregate (cost=0.10..0.10 rows=1 width=64) (actual time=136.506..136.613 rows=245 loops=1)
Group Key: t.barcode
-> Nested Loop Semi Join (cost=0.03..0.09 rows=1 width=306) (actual time=109.061..136.300 rows=268 loops=1)
Join Filter: (((t.barcode)::text = (shopinv_1.barcode)::text) AND (char_length((t.barname)::text) = (max(char_length((shopinv_1.barname)::text)))))
Rows Removed by Join Filter: 36206
-> CTE Scan on shopinv t (cost=0.00..0.02 rows=1 width=306) (actual time=0.002..7.831 rows=281 loops=1)
Filter: (isplatformgd = 'å?¦'::text)
Rows Removed by Filter: 45248
-> HashAggregate (cost=0.03..0.04 rows=1 width=36) (actual time=0.388..0.425 rows=130 loops=281)
Group Key: shopinv_1.barcode
-> CTE Scan on shopinv shopinv_1 (cost=0.00..0.02 rows=1 width=306) (actual time=0.002..108.746 rows=281 loops=1)
Filter: (isplatformgd = 'å?¦'::text)
Rows Removed by Filter: 45248
-> Nested Loop Left Join (cost=0.00..0.05 rows=1 width=120) (actual time=106687.477..112306.899 rows=45529 loops=1)
Join Filter: ((shopinv.barcode)::text = (lenthbarname.barcode)::text)
Rows Removed by Join Filter: 11153534
-> CTE Scan on shopinv (cost=0.00..0.02 rows=1 width=362) (actual time=106550.821..106569.406 rows=45529 loops=1)
-> CTE Scan on lenthbarname (cost=0.00..0.02 rows=1 width=64) (actual time=0.003..0.063 rows=245 loops=45529)
Planning time: 4.429 ms
Execution time: 112384.892 ms
(136 rows)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
