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)