explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8FI4

Settings
# exclusive inclusive rows x rows loops node
1. 20.767 1,228.352 ↑ 22.1 9 1

Gather (cost=32,170.16..43,874.33 rows=199 width=274) (actual time=1,215.102..1,228.352 rows=9 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 0.010 1,207.585 ↑ 27.7 3 3 / 3

Nested Loop Left Join (cost=31,170.16..42,854.43 rows=83 width=274) (actual time=1,204.884..1,207.585 rows=3 loops=3)

  • Join Filter: (shift_brokers.broker_id = fleets.id)
  • Filter: ((fleets.type IS NULL) OR ((fleets.type)::text <> 'Broker'::text) OR (((fleets.type)::text = 'Broker'::text) AND ((shift_brokers.retender_sequence IS NULL) OR (shift_brokers.retender_sequence = 0))))
3. 3.219 1,207.551 ↑ 33.3 3 3 / 3

Parallel Hash Join (cost=31,169.87..42,820.94 rows=100 width=297) (actual time=1,204.861..1,207.551 rows=3 loops=3)

  • Hash Cond: (timesheets_v2_customer_transactions.id = v2_timesheets.id)
4. 10.999 1,077.952 ↑ 1.1 22,104 3 / 3

Hash Join (cost=22,548.09..34,110.78 rows=23,456 width=301) (actual time=986.373..1,077.952 rows=22,104 loops=3)

  • Hash Cond: (timesheets_v2_customer_transactions.job_site_id = job_sites.id)
5. 9.706 1,041.869 ↑ 1.1 22,104 3 / 3

Hash Join (cost=21,435.31..32,936.41 rows=23,456 width=309) (actual time=961.182..1,041.869 rows=22,104 loops=3)

  • Hash Cond: (jobs_shifts.customer_id = customers.id)
6. 12.041 1,031.277 ↑ 1.1 22,104 3 / 3

Parallel Hash Join (cost=21,307.62..32,747.03 rows=23,456 width=313) (actual time=960.238..1,031.277 rows=22,104 loops=3)

  • Hash Cond: (shifts_v2_timesheets.job_id = jobs_shifts.id)
7. 35.503 983.273 ↑ 1.1 22,104 3 / 3

Parallel Hash Join (cost=16,916.64..28,294.47 rows=23,456 width=313) (actual time=924.165..983.273 rows=22,104 loops=3)

  • Hash Cond: (shifts_v2_timesheets.id = timesheets_v2_customer_transactions.shift_id)
8. 23.816 23.816 ↑ 1.2 75,835 3 / 3

Parallel Seq Scan on shifts shifts_v2_timesheets (cost=0.00..10,938.35 rows=91,135 width=8) (actual time=0.008..23.816 rows=75,835 loops=3)

9. 29.710 923.954 ↑ 1.1 22,104 3 / 3

Parallel Hash (cost=16,623.44..16,623.44 rows=23,456 width=317) (actual time=923.954..923.954 rows=22,104 loops=3)

  • Buckets: 131,072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 19,392kB
10. 15.461 894.244 ↑ 1.1 22,104 3 / 3

Parallel Hash Join (cost=12,175.10..16,623.44 rows=23,456 width=317) (actual time=815.213..894.244 rows=22,104 loops=3)

  • Hash Cond: (v2_customer_transactions.v2_timesheet_id = timesheets_v2_customer_transactions.id)
11. 9.617 103.608 ↑ 1.1 22,104 3 / 3

Hash Join (cost=6,465.29..10,852.06 rows=23,456 width=293) (actual time=39.662..103.608 rows=22,104 loops=3)

  • Hash Cond: (v2_earnings.fleet_id = fleets.id)
12. 27.006 90.736 ↑ 1.3 22,104 3 / 3

Parallel Hash Join (cost=6,236.19..10,548.14 rows=28,466 width=282) (actual time=36.364..90.736 rows=22,104 loops=3)

  • Hash Cond: (v2_earnings.id = v2_customer_transactions.v2_earning_id)
13. 27.557 27.557 ↑ 1.2 42,461 3 / 3

Parallel Seq Scan on v2_earnings (cost=0.00..3,995.76 rows=52,690 width=16) (actual time=0.008..27.557 rows=42,461 loops=3)

  • Filter: ((fleet_approval_status <> 0) OR (fleet_approval_status IS NULL))
  • Rows Removed by Filter: 1
14. 10.395 36.173 ↑ 1.3 22,106 3 / 3

Parallel Hash (cost=5,880.29..5,880.29 rows=28,472 width=274) (actual time=36.172..36.173 rows=22,106 loops=3)

  • Buckets: 131,072 Batches: 1 Memory Usage: 16,608kB
15. 25.778 25.778 ↑ 1.3 22,106 3 / 3

Parallel Seq Scan on v2_customer_transactions (cost=0.00..5,880.29 rows=28,472 width=274) (actual time=0.008..25.778 rows=22,106 loops=3)

  • Filter: ((NOT exported) AND (created_at >= '2019-01-01 00:00:00'::timestamp without time zone) AND (((type)::text = 'V2::AdjustmentTransaction'::text) OR (price_per_unit_cents = 0)))
  • Rows Removed by Filter: 27,258
16. 0.748 3.255 ↓ 1.2 3,448 3 / 3

Hash (cost=193.64..193.64 rows=2,837 width=19) (actual time=3.255..3.255 rows=3,448 loops=3)

  • Buckets: 4,096 Batches: 1 Memory Usage: 166kB
17. 2.507 2.507 ↓ 1.2 3,448 3 / 3

Seq Scan on fleets (cost=0.00..193.64 rows=2,837 width=19) (actual time=0.016..2.507 rows=3,448 loops=3)

  • Filter: ((type IS NULL) OR ((type)::text <> 'Broker'::text) OR ((type)::text = 'Broker'::text))
18. 269.081 775.175 ↑ 1.3 41,198 3 / 3

Parallel Hash (cost=5,023.25..5,023.25 rows=54,925 width=24) (actual time=775.175..775.175 rows=41,198 loops=3)

  • Buckets: 262,144 Batches: 1 Memory Usage: 8,896kB
19. 506.094 506.094 ↑ 1.3 41,198 3 / 3

Parallel Seq Scan on v2_timesheets timesheets_v2_customer_transactions (cost=0.00..5,023.25 rows=54,925 width=24) (actual time=0.008..506.094 rows=41,198 loops=3)

20. 17.054 35.963 ↑ 1.3 16,866 3 / 3

Parallel Hash (cost=4,126.55..4,126.55 rows=21,155 width=8) (actual time=35.963..35.963 rows=16,866 loops=3)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,560kB
21. 18.909 18.909 ↑ 1.3 16,866 3 / 3

Parallel Seq Scan on jobs jobs_shifts (cost=0.00..4,126.55 rows=21,155 width=8) (actual time=0.006..18.909 rows=16,866 loops=3)

22. 0.393 0.886 ↓ 1.0 2,081 3 / 3

Hash (cost=101.75..101.75 rows=2,075 width=4) (actual time=0.886..0.886 rows=2,081 loops=3)

  • Buckets: 4,096 Batches: 1 Memory Usage: 106kB
23. 0.493 0.493 ↓ 1.0 2,081 3 / 3

Seq Scan on customers (cost=0.00..101.75 rows=2,075 width=4) (actual time=0.009..0.493 rows=2,081 loops=3)

24. 2.714 25.084 ↓ 1.0 12,821 3 / 3

Hash (cost=952.90..952.90 rows=12,790 width=4) (actual time=25.084..25.084 rows=12,821 loops=3)

  • Buckets: 16,384 Batches: 1 Memory Usage: 579kB
25. 22.370 22.370 ↓ 1.0 12,821 3 / 3

Seq Scan on job_sites (cost=0.00..952.90 rows=12,790 width=4) (actual time=0.009..22.370 rows=12,821 loops=3)

26. 55.618 126.380 ↑ 33.6 7 3 / 3

Parallel Hash (cost=8,618.85..8,618.85 rows=235 width=12) (actual time=126.379..126.380 rows=7 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 72kB
27. 0.287 70.762 ↑ 33.6 7 3 / 3

Nested Loop (cost=0.84..8,618.85 rows=235 width=12) (actual time=14.036..70.762 rows=7 loops=3)

28. 0.338 34.851 ↓ 2.1 1,484 3 / 3

Nested Loop (cost=0.42..8,232.79 rows=715 width=4) (actual time=3.409..34.851 rows=1,484 loops=3)

29. 33.005 33.005 ↑ 1.4 116 3 / 3

Parallel Seq Scan on jobs (cost=0.00..4,179.43 rows=166 width=4) (actual time=3.384..33.005 rows=116 loops=3)

  • Filter: (customer_id = 563)
  • Rows Removed by Filter: 16,750
30. 1.508 1.508 ↓ 1.6 13 348 / 3

Index Scan using index_shifts_on_job_id on shifts (cost=0.42..24.34 rows=8 width=8) (actual time=0.005..0.013 rows=13 loops=348)

  • Index Cond: (job_id = jobs.id)
31. 35.624 35.624 ↓ 0.0 0 4,453 / 3

Index Scan using index_v2_timesheets_on_shift_id on v2_timesheets (cost=0.42..0.53 rows=1 width=16) (actual time=0.023..0.024 rows=0 loops=4,453)

  • Index Cond: (shift_id = shifts.id)
  • Filter: ((id IS NULL) OR ((status)::text = ANY ('{approved,invoiced}'::text[])))
  • Rows Removed by Filter: 0
32. 0.024 0.024 ↓ 0.0 0 9 / 3

Index Scan using index_shift_brokers_on_shift_id on shift_brokers (cost=0.29..0.31 rows=1 width=20) (actual time=0.008..0.008 rows=0 loops=9)

  • Index Cond: (shift_id = shifts.id)
Planning time : 6.352 ms
Execution time : 1,229.396 ms