Search Postgresql Archives

slow query on multiple table join

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux