explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XZ4d

Settings
# exclusive inclusive rows x rows loops node
1. 50.028 4,651.730 ↓ 455.0 455 1

Nested Loop (cost=61,749.39..213,228.51 rows=1 width=633) (actual time=2,027.589..4,651.730 rows=455 loops=1)

2. 0.000 4,351.802 ↓ 83,300.0 83,300 1

Nested Loop Left Join (cost=61,748.97..213,227.99 rows=1 width=80) (actual time=1,078.699..4,351.802 rows=83,300 loops=1)

3. 11.665 4,112.031 ↓ 83,300.0 83,300 1

Nested Loop Left Join (cost=61,748.55..213,227.43 rows=1 width=71) (actual time=1,078.688..4,112.031 rows=83,300 loops=1)

4. 103.087 3,800.206 ↓ 60,032.0 60,032 1

Hash Anti Join (cost=61,748.13..213,226.90 rows=1 width=75) (actual time=1,078.575..3,800.206 rows=60,032 loops=1)

  • Hash Cond: (account_assignments.sales_rep_id = users.id)
5. 0.000 3,490.816 ↓ 2.7 270,182 1

Nested Loop Left Join (cost=50,077.63..198,146.64 rows=99,135 width=79) (actual time=867.503..3,490.816 rows=270,182 loops=1)

6. 64.211 1,983.146 ↓ 2.2 217,455 1

Hash Left Join (cost=50,077.20..146,501.05 rows=99,135 width=75) (actual time=867.479..1,983.146 rows=217,455 loops=1)

  • Hash Cond: (buyer_vendors_public_orders.buyer_id = buyers.id)
7. 652.742 1,904.189 ↓ 2.2 217,455 1

Hash Right Join (cost=47,976.65..143,037.39 rows=99,135 width=60) (actual time=852.684..1,904.189 rows=217,455 loops=1)

  • Hash Cond: (accounts.id = buyer_vendors_public_orders.account_id)
8. 409.043 409.043 ↑ 1.0 1,784,136 1

Seq Scan on accounts (cost=0.00..50,140.91 rows=1,784,191 width=23) (actual time=0.209..409.043 rows=1,784,136 loops=1)

9. 74.116 842.404 ↓ 2.2 217,455 1

Hash (cost=45,865.47..45,865.47 rows=99,135 width=41) (actual time=842.404..842.404 rows=217,455 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 64 (originally 4) Memory Usage: 4035kB
10. 117.259 768.288 ↓ 2.2 217,455 1

Hash Left Join (cost=30,710.58..45,865.47 rows=99,135 width=41) (actual time=325.752..768.288 rows=217,455 loops=1)

  • Hash Cond: (orders.buyer_vendor_id = buyer_vendors_public_orders.id)
11. 179.090 461.604 ↓ 2.2 217,455 1

Hash Join (cost=20,438.07..31,441.85 rows=99,135 width=33) (actual time=136.100..461.604 rows=217,455 loops=1)

  • Hash Cond: (order_milestones.order_id = orders.id)
12. 62.157 154.039 ↓ 2.2 217,455 1

Hash Join (cost=1.05..7,268.72 rows=99,135 width=8) (actual time=0.020..154.039 rows=217,455 loops=1)

  • Hash Cond: (order_milestones.milestone_id = milestones.id)
13. 91.872 91.872 ↓ 1.0 297,407 1

Seq Scan on order_milestones (cost=0.00..5,161.05 rows=297,405 width=12) (actual time=0.007..91.872 rows=297,407 loops=1)

14. 0.001 0.010 ↑ 1.0 1 1

Hash (cost=1.04..1.04 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on milestones (cost=0.00..1.04 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: ((name)::text = 'submitted'::text)
  • Rows Removed by Filter: 2
16. 48.355 128.475 ↑ 1.0 233,428 1

Hash (cost=15,921.12..15,921.12 rows=233,512 width=29) (actual time=128.475..128.475 rows=233,428 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2323kB
17. 80.120 80.120 ↑ 1.0 233,428 1

Seq Scan on orders (cost=0.00..15,921.12 rows=233,512 width=29) (actual time=0.007..80.120 rows=233,428 loops=1)

18. 79.133 189.425 ↓ 1.0 253,537 1

Hash (cost=5,865.34..5,865.34 rows=253,534 width=12) (actual time=189.425..189.425 rows=253,537 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 3722kB
19. 110.292 110.292 ↓ 1.0 253,537 1

Seq Scan on buyer_vendors buyer_vendors_public_orders (cost=0.00..5,865.34 rows=253,534 width=12) (actual time=0.027..110.292 rows=253,537 loops=1)

20. 7.483 14.746 ↑ 1.0 54,291 1

Hash (cost=1,421.91..1,421.91 rows=54,291 width=23) (actual time=14.746..14.746 rows=54,291 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3519kB
21. 7.263 7.263 ↑ 1.0 54,291 1

Seq Scan on buyers (cost=0.00..1,421.91 rows=54,291 width=23) (actual time=0.008..7.263 rows=54,291 loops=1)

22. 1,522.185 1,522.185 ↑ 2.0 1 217,455

Index Scan using index_account_assignments_on_account_id on account_assignments (cost=0.43..0.50 rows=2 width=8) (actual time=0.006..0.007 rows=1 loops=217,455)

  • Index Cond: (account_id = accounts.id)
23. 33.514 206.303 ↑ 1.0 133,073 1

Hash (cost=9,486.89..9,486.89 rows=133,089 width=4) (actual time=206.303..206.303 rows=133,073 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3373kB
24. 172.789 172.789 ↑ 1.0 133,073 1

Seq Scan on users (cost=0.00..9,486.89 rows=133,089 width=4) (actual time=0.009..172.789 rows=133,073 loops=1)

25. 300.160 300.160 ↑ 2.0 1 60,032

Index Only Scan using index_order_milestones_on_order_id on order_milestones order_milestones_public_orders (cost=0.42..0.51 rows=2 width=4) (actual time=0.004..0.005 rows=1 loops=60,032)

  • Index Cond: (order_id = orders.id)
  • Heap Fetches: 83300
26. 249.900 249.900 ↑ 1.0 1 83,300

Index Scan using users_pkey on users created_bies_public_order_milestones (cost=0.42..0.55 rows=1 width=17) (actual time=0.002..0.003 rows=1 loops=83,300)

  • Index Cond: (id = order_milestones.created_by_id)
27. 249.900 249.900 ↓ 0.0 0 83,300

Index Scan using buyer_vendors_pkey on buyer_vendors (cost=0.42..0.51 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=83,300)

  • Index Cond: (id = orders.buyer_vendor_id)
  • Filter: (vendor_id = 30)
  • Rows Removed by Filter: 1