explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N47x

Settings
# exclusive inclusive rows x rows loops node
1. 0.078 34,492.388 ↑ 2,500.0 208 1

WindowAgg (cost=6,078,811.88..6,089,211.88 rows=520,000 width=313) (actual time=34,492.307..34,492.388 rows=208 loops=1)

2.          

CTE x

3. 414.282 34,100.711 ↑ 5.4 160,374 1

Finalize GroupAggregate (cost=3,701,244.80..4,251,981.77 rows=858,192 width=81) (actual time=25,349.950..34,100.711 rows=160,374 loops=1)

  • Group Key: m.shop_no, m.allotment_month, m.product_id
4. 0.000 33,686.429 ↑ 3.6 477,188 1

Gather Merge (cost=3,701,244.80..4,204,781.21 rows=1,716,384 width=81) (actual time=25,349.928..33,686.429 rows=477,188 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 10,666.614 99,141.252 ↑ 5.4 159,063 3

Partial GroupAggregate (cost=3,700,244.78..4,005,667.88 rows=858,192 width=81) (actual time=25,312.544..33,047.084 rows=159,063 loops=3)

  • Group Key: m.shop_no, m.allotment_month, m.product_id
6. 76,827.732 88,474.638 ↑ 1.3 15,476,177 3

Sort (cost=3,700,244.78..3,749,003.15 rows=19,503,348 width=25) (actual time=25,312.528..29,491.546 rows=15,476,177 loops=3)

  • Sort Key: m.shop_no, m.product_id
  • Sort Method: external merge Disk: 539320kB
  • Worker 0: Sort Method: external merge Disk: 536440kB
  • Worker 1: Sort Method: external merge Disk: 539480kB
7. 3,287.199 11,646.906 ↑ 1.3 15,476,177 3

Parallel Append (cost=0.00..872,026.58 rows=19,503,348 width=25) (actual time=0.046..3,882.302 rows=15,476,177 loops=3)

8. 8,359.707 8,359.707 ↑ 1.3 15,476,177 3

Parallel Seq Scan on member_entitlement_p_201907 m (cost=0.00..774,509.84 rows=19,503,348 width=25) (actual time=0.045..2,786.569 rows=15,476,177 loops=3)

  • Filter: (allotment_month = '2019-07-01 00:00:00'::timestamp without time zone)
9. 0.100 34,492.310 ↑ 2,500.0 208 1

Sort (cost=1,826,830.11..1,828,130.11 rows=520,000 width=301) (actual time=34,492.300..34,492.310 rows=208 loops=1)

  • Sort Key: a.level2, s.commodity_name_en
  • Sort Method: quicksort Memory: 45kB
10. 36.958 34,492.210 ↑ 2,500.0 208 1

GroupAggregate (cost=1,608,286.48..1,704,587.41 rows=520,000 width=301) (actual time=34,444.209..34,492.210 rows=208 loops=1)

  • Group Key: a.level2, x.allotment_month, s.commodity_name_en
11. 103.478 34,455.252 ↑ 36.8 160,370 1

Sort (cost=1,608,286.48..1,623,036.64 rows=5,900,062 width=301) (actual time=34,444.042..34,455.252 rows=160,370 loops=1)

  • Sort Key: a.level2, x.allotment_month, s.commodity_name_en
  • Sort Method: quicksort Memory: 19502kB
12. 34.933 34,351.774 ↑ 36.8 160,370 1

Merge Join (cost=29,334.50..117,941.20 rows=5,900,062 width=301) (actual time=34,306.714..34,351.774 rows=160,370 loops=1)

  • Merge Cond: ((a.fps_dealer_id)::text = (x.shop_no)::text)
13. 31.005 57.996 ↑ 1.0 21,125 1

Sort (cost=3,995.61..4,048.50 rows=21,154 width=16) (actual time=55.795..57.996 rows=21,125 loops=1)

  • Sort Key: a.fps_dealer_id
  • Sort Method: quicksort Memory: 2077kB
14. 2.195 26.991 ↑ 1.0 21,125 1

Subquery Scan on a (cost=2,052.76..2,475.84 rows=21,154 width=16) (actual time=18.922..26.991 rows=21,125 loops=1)

15. 20.769 24.796 ↑ 1.0 21,125 1

HashAggregate (cost=2,052.76..2,264.30 rows=21,154 width=69) (actual time=18.921..24.796 rows=21,125 loops=1)

  • Group Key: ard_mapping_history.level1, ard_mapping_history.level11, ard_mapping_history.level12, ard_mapping_history.level2, ard_mapping_history.level21, ard_mapping_history.level3, ard_mapping_history.fps_dealer_id
16. 4.027 4.027 ↑ 1.0 22,433 1

Seq Scan on ard_mapping_history (cost=0.00..1,659.64 rows=22,464 width=69) (actual time=0.004..4.027 rows=22,433 loops=1)

17. 56.475 34,258.845 ↓ 2.9 160,374 1

Sort (cost=25,338.89..25,478.34 rows=55,782 width=348) (actual time=34,250.911..34,258.845 rows=160,374 loops=1)

  • Sort Key: x.shop_no
  • Sort Method: quicksort Memory: 18674kB
18. 43.919 34,202.370 ↓ 2.9 160,374 1

Hash Join (cost=1.29..20,941.17 rows=55,782 width=348) (actual time=25,349.987..34,202.370 rows=160,374 loops=1)

  • Hash Cond: (x.product_id = s.commodity_code)
19. 34,158.433 34,158.433 ↑ 5.4 160,374 1

CTE Scan on x (cost=0.00..17,163.84 rows=858,192 width=134) (actual time=25,349.952..34,158.433 rows=160,374 loops=1)

20. 0.004 0.018 ↑ 1.3 10 1

Hash (cost=1.13..1.13 rows=13 width=222) (actual time=0.017..0.018 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.014 0.014 ↑ 1.3 10 1

Seq Scan on stock_group_master s (cost=0.00..1.13 rows=13 width=222) (actual time=0.012..0.014 rows=10 loops=1)

Planning time : 2.280 ms
Execution time : 34,929.237 ms