explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FxEU

Settings
# exclusive inclusive rows x rows loops node
1. 10.373 16,081.538 ↓ 2.6 3,856 1

HashAggregate (cost=7,984,399.55..7,984,450.58 rows=1,458 width=132) (actual time=16,076.348..16,081.538 rows=3,856 loops=1)

  • Group Key: c.purchase_article_id, c.business_unit_id, c.date
2.          

CTE c

3. 292.376 16,065.743 ↑ 2.3 6,446 1

Recursive Union (cost=52,138.46..7,983,925.79 rows=14,577 width=168) (actual time=4,322.224..16,065.743 rows=6,446 loops=1)

4. 270.985 5,815.737 ↓ 4.0 6,446 1

Gather (cost=52,138.46..541,557.53 rows=1,627 width=159) (actual time=4,322.218..5,815.737 rows=6,446 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 4.019 5,544.752 ↓ 3.2 2,149 3 / 3

Nested Loop Left Join (cost=51,138.46..540,378.22 rows=678 width=159) (actual time=4,550.532..5,544.752 rows=2,149 loops=3)

6. 3.701 5,532.288 ↓ 3.1 2,111 3 / 3

Nested Loop (cost=51,137.90..539,898.73 rows=675 width=124) (actual time=4,550.489..5,532.288 rows=2,111 loops=3)

  • Join Filter: (dbp.valid_during @> d.d)
  • Rows Removed by Join Filter: 7,008
7. 185.145 5,528.587 ↑ 1.5 4,560 3 / 3

Hash Join (cost=51,137.90..524,708.98 rows=6,751 width=133) (actual time=4,381.471..5,528.587 rows=4,560 loops=3)

  • Hash Cond: ((sa.purchase_article_id = pa.id) AND (db.content_trading_unit_id = pa.trading_unit_id))
8. 779.506 5,130.062 ↑ 14.3 4,560 3 / 3

Hash Join (cost=43,987.92..513,712.14 rows=65,117 width=136) (actual time=3,827.011..5,130.062 rows=4,560 loops=3)

  • Hash Cond: (dbp.delivery_bundle_id = db.id)
9. 850.570 850.570 ↑ 1.3 9,371,513 3 / 3

Parallel Seq Scan on delivery_bundle_price dbp (cost=0.00..424,989.50 rows=11,755,550 width=51) (actual time=0.005..850.570 rows=9,371,513 loops=3)

10. 0.080 3,499.986 ↑ 13.4 102 3 / 3

Hash (cost=43,970.81..43,970.81 rows=1,369 width=101) (actual time=3,499.986..3,499.986 rows=102 loops=3)

  • Buckets: 2,048 Batches: 1 Memory Usage: 30kB
11. 39.185 3,499.906 ↑ 13.4 102 3 / 3

Hash Join (cost=18,291.53..43,970.81 rows=1,369 width=101) (actual time=582.818..3,499.906 rows=102 loops=3)

  • Hash Cond: (sa.purchase_article_id = ids.id)
12. 2,997.004 3,460.697 ↓ 1.0 248,297 3 / 3

Hash Join (cost=18,289.28..43,028.40 rows=247,058 width=85) (actual time=418.738..3,460.697 rows=248,297 loops=3)

  • Hash Cond: (db.supplier_article_id = sa.id)
13. 45.400 45.400 ↓ 1.0 248,297 3 / 3

Seq Scan on delivery_bundle db (cost=0.00..16,958.58 rows=247,058 width=69) (actual time=0.023..45.400 rows=248,297 loops=3)

14. 379.892 418.293 ↓ 1.0 197,708 3 / 3

Hash (cost=14,499.68..14,499.68 rows=195,968 width=32) (actual time=418.293..418.293 rows=197,708 loops=3)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,611kB
15. 38.401 38.401 ↓ 1.0 197,708 3 / 3

Seq Scan on supplier_article sa (cost=0.00..14,499.68 rows=195,968 width=32) (actual time=0.009..38.401 rows=197,708 loops=3)

16. 0.005 0.024 ↑ 4.0 25 3 / 3

Hash (cost=1.00..1.00 rows=100 width=16) (actual time=0.024..0.024 rows=25 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
17. 0.019 0.019 ↑ 4.0 25 3 / 3

Function Scan on unnest ids (cost=0.00..1.00 rows=100 width=16) (actual time=0.016..0.019 rows=25 loops=3)

  • Filter: (id IS NOT NULL)
18. 175.578 213.380 ↓ 1.0 109,434 3 / 3

Hash (cost=4,549.79..4,549.79 rows=109,279 width=45) (actual time=213.379..213.380 rows=109,434 loops=3)

  • Buckets: 65,536 Batches: 4 Memory Usage: 2,468kB
19. 37.802 37.802 ↓ 1.0 109,434 3 / 3

Seq Scan on purchase_article pa (cost=0.00..4,549.79 rows=109,279 width=45) (actual time=0.008..37.802 rows=109,434 loops=3)

20. 0.000 0.000 ↑ 50.0 2 13,679 / 3

Function Scan on unnest d (cost=0.00..1.00 rows=100 width=4) (actual time=0.000..0.000 rows=2 loops=13,679)

21. 8.445 8.445 ↑ 2.0 1 6,334 / 3

Index Only Scan using delivery_bundle_price_busines_delivery_bundle_price_id_busi_key on delivery_bundle_price_business_unit dbpbu (cost=0.56..0.67 rows=2 width=32) (actual time=0.004..0.004 rows=1 loops=6,334)

  • Index Cond: (delivery_bundle_price_id = dbp.id)
  • Heap Fetches: 1,192
22. 596.380 9,957.630 ↓ 324.3 419,916 1

Nested Loop Left Join (cost=28,393.47..744,207.67 rows=1,295 width=168) (actual time=1,023.452..9,957.630 rows=419,916 loops=1)

23. 237.287 8,521.418 ↓ 325.5 419,916 1

Nested Loop (cost=28,392.90..743,278.37 rows=1,290 width=147) (actual time=1,023.416..8,521.418 rows=419,916 loops=1)

  • Join Filter: (dbp_1.valid_during @> d_1.d)
  • Rows Removed by Join Filter: 996,828
24. 0.013 0.013 ↑ 50.0 2 1

Function Scan on unnest d_1 (cost=0.00..1.00 rows=100 width=4) (actual time=0.011..0.013 rows=2 loops=1)

25. 2,717.327 8,284.118 ↓ 54.9 708,372 2

Materialize (cost=28,392.90..723,953.62 rows=12,904 width=156) (actual time=511.712..4,142.059 rows=708,372 loops=2)

26. 2,320.005 5,566.791 ↓ 54.9 708,372 1

Hash Join (cost=28,392.90..723,889.10 rows=12,904 width=156) (actual time=1,023.395..5,566.791 rows=708,372 loops=1)

  • Hash Cond: (dbp_1.delivery_bundle_id = db_1.id)
27. 2,346.202 2,346.202 ↑ 1.0 28,114,538 1

Seq Scan on delivery_bundle_price dbp_1 (cost=0.00..589,567.21 rows=28,213,321 width=51) (actual time=0.007..2,346.202 rows=28,114,538 loops=1)

28. 0.981 900.584 ↓ 22.9 2,590 1

Hash (cost=28,391.49..28,391.49 rows=113 width=121) (actual time=900.584..900.584 rows=2,590 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 379kB
29. 221.324 899.603 ↓ 22.9 2,590 1

Hash Join (cost=23,560.45..28,391.49 rows=113 width=121) (actual time=678.262..899.603 rows=2,590 loops=1)

  • Hash Cond: ((c_1.supplier_article_id = db_1.supplier_article_id) AND (c_1.trading_unit_id = db_1.content_trading_unit_id))
  • Join Filter: (db_1.id <> c_1.delivery_bundle_id)
  • Rows Removed by Join Filter: 3,724
30. 1.175 1.175 ↑ 2.5 6,446 1

WorkTable Scan on c c_1 (cost=0.00..325.40 rows=16,270 width=100) (actual time=0.003..1.175 rows=6,446 loops=1)

31. 616.851 677.104 ↓ 1.0 248,297 1

Hash (cost=16,958.58..16,958.58 rows=247,058 width=69) (actual time=677.104..677.104 rows=248,297 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,591kB
32. 60.253 60.253 ↓ 1.0 248,297 1

Seq Scan on delivery_bundle db_1 (cost=0.00..16,958.58 rows=247,058 width=69) (actual time=0.005..60.253 rows=248,297 loops=1)

33. 839.832 839.832 ↑ 2.0 1 419,916

Index Only Scan using delivery_bundle_price_busines_delivery_bundle_price_id_busi_key on delivery_bundle_price_business_unit dbpbu_1 (cost=0.56..0.67 rows=2 width=32) (actual time=0.002..0.002 rows=1 loops=419,916)

  • Index Cond: (delivery_bundle_price_id = dbp_1.id)
  • Heap Fetches: 419,916
34. 16,071.165 16,071.165 ↑ 2.3 6,446 1

CTE Scan on c (cost=0.00..291.54 rows=14,577 width=68) (actual time=4,322.226..16,071.165 rows=6,446 loops=1)

Planning time : 2.311 ms
Execution time : 16,249.403 ms