explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 24p

Settings
# exclusive inclusive rows x rows loops node
1. 0.078 38,499.061 ↑ 3.0 208 1

WindowAgg (cost=4,709,268.54..4,709,281.02 rows=624 width=317) (actual time=38,498.980..38,499.061 rows=208 loops=1)

2. 0.177 38,498.983 ↑ 3.0 208 1

Sort (cost=4,709,268.54..4,709,270.10 rows=624 width=305) (actual time=38,498.973..38,498.983 rows=208 loops=1)

  • Sort Key: a.level2, s.commodity_name_en
  • Sort Method: quicksort Memory: 52kB
3. 0.049 38,498.806 ↑ 3.0 208 1

Hash Join (cost=4,706,333.17..4,709,239.57 rows=624 width=305) (actual time=38,497.962..38,498.806 rows=208 loops=1)

  • Hash Cond: (m.product_id = s.commodity_code)
4. 0.000 38,498.744 ↑ 46.2 208 1

Finalize GroupAggregate (cost=4,706,331.88..4,709,100.03 rows=9,600 width=87) (actual time=38,497.943..38,498.744 rows=208 loops=1)

  • Group Key: a.level2, m.allotment_month, m.product_id
5. 0.000 38,572.050 ↑ 30.8 624 1

Gather Merge (cost=4,706,331.88..4,708,572.03 rows=19,200 width=87) (actual time=38,497.923..38,572.050 rows=624 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.549 114,869.187 ↑ 46.2 208 3

Sort (cost=4,705,331.85..4,705,355.85 rows=9,600 width=87) (actual time=38,289.714..38,289.729 rows=208 loops=3)

  • Sort Key: a.level2, m.product_id
  • Sort Method: quicksort Memory: 60kB
  • Worker 0: Sort Method: quicksort Memory: 60kB
  • Worker 1: Sort Method: quicksort Memory: 60kB
7. 14,006.169 114,868.638 ↑ 46.2 208 3

Partial HashAggregate (cost=4,704,552.87..4,704,696.87 rows=9,600 width=87) (actual time=38,289.353..38,289.546 rows=208 loops=3)

  • Group Key: a.level2, m.allotment_month, m.product_id
8. 12,369.744 100,862.469 ↑ 1.5 15,476,175 3

Merge Join (cost=3,972,443.67..4,416,102.09 rows=23,076,062 width=31) (actual time=25,515.482..33,620.823 rows=15,476,175 loops=3)

  • Merge Cond: ((m.shop_no)::text = (a.fps_dealer_id)::text)
9. 65,727.948 85,984.095 ↑ 1.3 15,476,177 3

Sort (cost=3,968,448.06..4,017,206.80 rows=19,503,495 width=25) (actual time=25,455.355..28,661.365 rows=15,476,177 loops=3)

  • Sort Key: m.shop_no
  • Sort Method: external merge Disk: 539400kB
  • Worker 0: Sort Method: external merge Disk: 539392kB
  • Worker 1: Sort Method: external merge Disk: 536432kB
10. 8,143.839 20,256.147 ↑ 1.3 15,476,177 3

Hash Join (cost=1.29..1,140,207.51 rows=19,503,495 width=25) (actual time=0.076..6,752.049 rows=15,476,177 loops=3)

  • Hash Cond: (m.product_id = sgm.commodity_code)
11. 3,541.029 12,112.245 ↑ 1.3 15,476,177 3

Parallel Append (cost=0.00..872,033.16 rows=19,503,495 width=25) (actual time=0.043..4,037.415 rows=15,476,177 loops=3)

12. 8,571.216 8,571.216 ↑ 1.3 15,476,177 3

Parallel Seq Scan on member_entitlement_p_201907 m (cost=0.00..774,515.68 rows=19,503,495 width=25) (actual time=0.042..2,857.072 rows=15,476,177 loops=3)

  • Filter: (allotment_month = '2019-07-01 00:00:00'::timestamp without time zone)
13. 0.021 0.063 ↑ 1.3 10 3

Hash (cost=1.13..1.13 rows=13 width=4) (actual time=0.020..0.021 rows=10 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.042 0.042 ↑ 1.3 10 3

Seq Scan on stock_group_master sgm (cost=0.00..1.13 rows=13 width=4) (actual time=0.012..0.014 rows=10 loops=3)

15. 2,418.351 2,508.630 ↓ 731.6 15,475,770 3

Sort (cost=3,995.61..4,048.50 rows=21,154 width=16) (actual time=60.115..836.210 rows=15,475,770 loops=3)

  • Sort Key: a.fps_dealer_id
  • Sort Method: quicksort Memory: 2077kB
  • Worker 0: Sort Method: quicksort Memory: 2077kB
  • Worker 1: Sort Method: quicksort Memory: 2077kB
16. 5.697 90.279 ↑ 1.0 21,125 3

Subquery Scan on a (cost=2,052.76..2,475.84 rows=21,154 width=16) (actual time=22.646..30.093 rows=21,125 loops=3)

17. 69.153 84.582 ↑ 1.0 21,125 3

HashAggregate (cost=2,052.76..2,264.30 rows=21,154 width=69) (actual time=22.645..28.194 rows=21,125 loops=3)

  • 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
18. 15.429 15.429 ↑ 1.0 22,433 3

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

19. 0.004 0.013 ↑ 1.3 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.009 0.009 ↑ 1.3 10 1

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

Planning time : 0.652 ms
Execution time : 38,738.895 ms