explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8mC

Settings
# exclusive inclusive rows x rows loops node
1. 319.066 9,294.440 ↑ 3,925.1 2,446 1

GroupAggregate (cost=1,882,299.50..2,146,324.55 rows=9,600,911 width=26) (actual time=8,023.763..9,294.440 rows=2,446 loops=1)

  • Group Key: performance_production.product_category_id, performance_production.product_line_id, (date_trunc('month'::text, (performance_inspection.completed_at)::timestamp with time zone))
2. 3,375.898 8,975.374 ↑ 5.5 1,758,773 1

Sort (cost=1,882,299.50..1,906,301.78 rows=9,600,911 width=22) (actual time=8,023.733..8,975.374 rows=1,758,773 loops=1)

  • Sort Key: performance_production.product_category_id, performance_production.product_line_id, (date_trunc('month'::text, (performance_inspection.completed_at)::timestamp with time zone))
  • Sort Method: external merge Disk: 65328kB
3. 2,984.890 5,599.476 ↑ 5.5 1,758,773 1

Hash Join (cost=135,221.58..571,950.33 rows=9,600,911 width=22) (actual time=1,515.059..5,599.476 rows=1,758,773 loops=1)

  • Hash Cond: (performance_production.purchase_order_id = performance_purchaseorder.id)
4. 1,101.134 1,101.134 ↓ 1.0 9,602,441 1

Seq Scan on performance_production (cost=0.00..332,090.11 rows=9,600,911 width=30) (actual time=0.014..1,101.134 rows=9,602,441 loops=1)

5. 33.468 1,513.452 ↑ 1.9 104,532 1

Hash (cost=132,801.16..132,801.16 rows=193,634 width=36) (actual time=1,513.452..1,513.452 rows=104,532 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8990kB
6. 40.332 1,479.984 ↑ 1.9 104,532 1

Hash Left Join (cost=125,446.13..132,801.16 rows=193,634 width=36) (actual time=1,343.530..1,479.984 rows=104,532 loops=1)

  • Hash Cond: (performance_inspection_purchase_orders.inspection_id = performance_inspection.id)
7. 79.723 1,401.868 ↑ 1.9 104,532 1

Hash Right Join (cost=121,316.75..126,009.31 rows=193,634 width=48) (actual time=1,305.022..1,401.868 rows=104,532 loops=1)

  • Hash Cond: (performance_inspection_purchase_orders.purchaseorder_id = performance_purchaseorder.id)
8. 18.337 18.337 ↑ 1.0 185,707 1

Seq Scan on performance_inspection_purchase_orders (cost=0.00..3,550.34 rows=193,634 width=32) (actual time=0.017..18.337 rows=185,707 loops=1)

9. 24.368 1,303.808 ↑ 2.5 75,964 1

Hash (cost=118,896.33..118,896.33 rows=193,634 width=32) (actual time=1,303.808..1,303.808 rows=75,964 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 6796kB
10. 211.109 1,279.440 ↑ 2.5 75,964 1

Hash Join (cost=94,702.64..118,896.33 rows=193,634 width=32) (actual time=979.120..1,279.440 rows=75,964 loops=1)

  • Hash Cond: (performance_purchaseorder.id = v0.id)
11. 90.690 90.690 ↑ 1.0 898,256 1

Seq Scan on performance_purchaseorder (cost=0.00..18,878.16 rows=901,116 width=16) (actual time=0.020..90.690 rows=898,256 loops=1)

12. 16.375 977.641 ↑ 2.5 75,964 1

Hash (cost=92,282.22..92,282.22 rows=193,634 width=16) (actual time=977.641..977.641 rows=75,964 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 5609kB
13. 12.634 961.266 ↑ 2.5 75,964 1

Unique (cost=89,377.71..90,345.88 rows=193,634 width=16) (actual time=928.594..961.266 rows=75,964 loops=1)

14. 58.435 948.632 ↑ 1.9 102,193 1

Sort (cost=89,377.71..89,861.79 rows=193,634 width=16) (actual time=928.593..948.632 rows=102,193 loops=1)

  • Sort Key: v0.id
  • Sort Method: quicksort Memory: 7863kB
15. 158.952 890.197 ↑ 1.9 102,193 1

Hash Join (cost=57,586.99..72,373.76 rows=193,634 width=16) (actual time=663.867..890.197 rows=102,193 loops=1)

  • Hash Cond: (v1.purchaseorder_id = v0.id)
16. 52.226 393.548 ↑ 1.9 102,193 1

Hash Join (cost=23,044.88..28,877.19 rows=193,634 width=16) (actual time=324.258..393.548 rows=102,193 loops=1)

  • Hash Cond: (v1.inspection_id = u0.id)
17. 17.310 17.310 ↑ 1.0 185,707 1

Seq Scan on performance_inspection_purchase_orders v1 (cost=0.00..3,550.34 rows=193,634 width=32) (actual time=0.012..17.310 rows=185,707 loops=1)

18. 6.909 324.012 ↑ 1.7 41,588 1

Hash (cost=22,137.65..22,137.65 rows=72,578 width=16) (actual time=324.011..324.012 rows=41,588 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 2974kB
19. 27.451 317.103 ↑ 1.7 41,588 1

HashAggregate (cost=21,411.87..22,137.65 rows=72,578 width=16) (actual time=305.847..317.103 rows=41,588 loops=1)

  • Group Key: u0.id
20. 66.482 289.652 ↑ 4.5 41,673 1

Hash Right Join (cost=12,062.68..20,943.99 rows=187,153 width=16) (actual time=197.021..289.652 rows=41,673 loops=1)

  • Hash Cond: (u2.inspection_id = u0.id)
  • Filter: ((u0.owner_id = 252437) OR (u2.executor_id = 252437) OR (u0.factory_id = 252437) OR (u0.supplier_id = 252437) OR (u0.retailer_id = 252437) OR (u7.organization_id = 252437) OR (u9.organization_id = 252437) OR (u0.booking_sender_id = 252437))
  • Rows Removed by Filter: 31061
21. 26.470 26.470 ↑ 1.0 232,098 1

Seq Scan on performance_booking u2 (cost=0.00..5,582.38 rows=233,138 width=20) (actual time=0.022..26.470 rows=232,098 loops=1)

22. 21.078 196.700 ↑ 1.2 72,734 1

Hash (cost=11,011.31..11,011.31 rows=84,110 width=44) (actual time=196.700..196.700 rows=72,734 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6346kB
23. 39.700 175.622 ↑ 1.2 72,734 1

Hash Right Join (cost=8,125.02..11,011.31 rows=84,110 width=44) (actual time=128.624..175.622 rows=72,734 loops=1)

  • Hash Cond: (u7.inspection_id = u0.id)
24. 8.011 8.011 ↑ 1.1 94,663 1

Seq Scan on performance_inspection_shared_organizations u7 (cost=0.00..1,695.90 rows=103,590 width=20) (actual time=0.027..8.011 rows=94,663 loops=1)

25. 23.446 127.911 ↑ 1.0 71,872 1

Hash (cost=7,208.24..7,208.24 rows=73,342 width=40) (actual time=127.911..127.911 rows=71,872 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6004kB
26. 44.995 104.465 ↑ 1.0 71,872 1

Hash Right Join (cost=4,584.97..7,208.24 rows=73,342 width=40) (actual time=52.155..104.465 rows=71,872 loops=1)

  • Hash Cond: (u9.inspection_id = u0.id)
27. 7.816 7.816 ↑ 1.0 89,717 1

Seq Scan on performance_inspection_assignees u9 (cost=0.00..1,552.79 rows=91,279 width=20) (actual time=0.019..7.816 rows=89,717 loops=1)

28. 21.458 51.654 ↑ 1.0 71,872 1

Hash (cost=3,677.74..3,677.74 rows=72,578 width=36) (actual time=51.653..51.654 rows=71,872 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5723kB
29. 30.196 30.196 ↑ 1.0 71,872 1

Seq Scan on performance_inspection u0 (cost=0.00..3,677.74 rows=72,578 width=36) (actual time=0.038..30.196 rows=71,872 loops=1)

  • Filter: ((completed_at >= '2018-11-06'::date) AND (completed_at <= '2019-11-05'::date) AND ((status)::text = 'completed'::text))
  • Rows Removed by Filter: 17717
30. 211.621 337.697 ↑ 1.0 898,256 1

Hash (cost=18,878.16..18,878.16 rows=901,116 width=16) (actual time=337.697..337.697 rows=898,256 loops=1)

  • Buckets: 1048576 Batches: 2 Memory Usage: 29284kB
31. 126.076 126.076 ↑ 1.0 898,256 1

Seq Scan on performance_purchaseorder v0 (cost=0.00..18,878.16 rows=901,116 width=16) (actual time=0.021..126.076 rows=898,256 loops=1)

32. 19.438 37.784 ↑ 1.0 89,589 1

Hash (cost=3,000.28..3,000.28 rows=90,328 width=20) (actual time=37.784..37.784 rows=89,589 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5574kB
33. 18.346 18.346 ↑ 1.0 89,589 1

Seq Scan on performance_inspection (cost=0.00..3,000.28 rows=90,328 width=20) (actual time=0.010..18.346 rows=89,589 loops=1)

Planning time : 2.634 ms
Execution time : 9,314.519 ms