explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z5J4F

Settings
# exclusive inclusive rows x rows loops node
1. 2,989.804 24,433.347 ↑ 5.0 585,407 1

Sort (cost=148,433,285.94..148,440,617.32 rows=2,932,551 width=1,041) (actual time=24,023.347..24,433.347 rows=585,407 loops=1)

  • Sort Key: ride_details.appt_id, ride_details.id
  • Sort Method: external merge Disk: 289312kB
  • Buffers: shared hit=1496196 read=56 dirtied=1, temp read=180483 written=181008
  • I/O Timings: read=4578.392
2. 7,588.929 21,443.543 ↑ 5.0 585,407 1

Hash Left Join (cost=88,743.93..144,088,879.35 rows=2,932,551 width=1,041) (actual time=5,304.406..21,443.543 rows=585,407 loops=1)

  • Hash Cond: ((ride_details.appt_timezone)::text = pg_timezone_names.name)
  • Buffers: shared hit=1496193 read=56 dirtied=1, temp read=88613 written=89022
  • I/O Timings: read=4578.392
3. 0.000 5,641.411 ↑ 1.0 585,407 1

Gather (cost=88,721.43..391,776.53 rows=587,509 width=811) (actual time=5,028.694..5,641.411 rows=585,407 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=156959 dirtied=1, temp read=88613 written=89022
4. 260.680 7,205.378 ↑ 1.3 195,136 3 / 3

Hash Left Join (cost=87,721.43..332,025.63 rows=244,795 width=811) (actual time=4,996.074..7,205.378 rows=195,136 loops=3)

  • Hash Cond: (((appointments.additional_details ->> 'requesting_organization'::text))::integer = req_org.id)
  • Buffers: shared hit=156959 dirtied=1, temp read=88613 written=89022
5. 233.825 6,939.624 ↑ 1.3 195,136 3 / 3

Hash Left Join (cost=87,422.39..330,991.50 rows=244,795 width=793) (actual time=4,990.952..6,939.624 rows=195,136 loops=3)

  • Hash Cond: (dynamite_claims.payer_id = org_payers.id)
  • Buffers: shared hit=156401 dirtied=1, temp read=88613 written=89022
6. 228.522 6,705.700 ↑ 1.3 195,136 3 / 3

Hash Left Join (cost=87,419.65..330,344.74 rows=244,795 width=772) (actual time=4,990.833..6,705.700 rows=195,136 loops=3)

  • Hash Cond: (dynamite_claims.claims_adjuster_id = dynamite_claims_adjuster.id)
  • Buffers: shared hit=156398 dirtied=1, temp read=88613 written=89022
7. 307.158 6,474.936 ↑ 1.3 195,136 3 / 3

Hash Left Join (cost=87,348.01..329,630.48 rows=244,795 width=749) (actual time=4,988.556..6,474.936 rows=195,136 loops=3)

  • Hash Cond: (appointments.dynamite_claims_id = dynamite_claims.id)
  • Buffers: shared hit=156329 dirtied=1, temp read=88613 written=89022
8. 246.215 6,052.113 ↑ 1.3 195,136 3 / 3

Hash Left Join (cost=84,907.31..281,580.18 rows=244,795 width=719) (actual time=4,872.503..6,052.113 rows=195,136 loops=3)

  • Hash Cond: (ride_details.ride_requester_id = users.id)
  • Buffers: shared hit=154788 dirtied=1, temp read=85163 written=85572
9. 246.559 5,752.818 ↑ 1.3 195,136 3 / 3

Hash Left Join (cost=83,352.32..279,382.55 rows=244,795 width=696) (actual time=4,819.350..5,752.818 rows=195,136 loops=3)

  • Hash Cond: (organisations.market_segment_id = market_segments.id)
  • Buffers: shared hit=152112 dirtied=1, temp read=85163 written=85572
10. 276.961 5,505.743 ↑ 1.3 195,136 3 / 3

Hash Left Join (cost=83,335.11..278,716.76 rows=244,795 width=682) (actual time=4,818.824..5,505.743 rows=195,136 loops=3)

  • Hash Cond: (ride_details.org_id = organisations.id)
  • Buffers: shared hit=152091 dirtied=1, temp read=85163 written=85572
11. 963.264 5,216.692 ↑ 1.3 195,136 3 / 3

Parallel Hash Join (cost=83,036.07..277,774.59 rows=244,795 width=599) (actual time=4,806.716..5,216.692 rows=195,136 loops=3)

  • Hash Cond: (ride_details.appt_id = appointments.id)
  • Buffers: shared hit=151533 dirtied=1, temp read=85163 written=85572
12. 986.310 3,190.075 ↑ 1.3 195,149 3 / 3

Parallel Hash Left Join (cost=31,301.76..190,579.87 rows=244,795 width=474) (actual time=2,720.577..3,190.075 rows=195,149 loops=3)

  • Hash Cond: (ride_details.patient_id = patients.id)
  • Buffers: shared hit=119780, temp read=50963 written=51080
13. 1,000.173 2,001.012 ↑ 1.3 195,149 3 / 3

Parallel Hash Join (cost=21,014.89..151,320.40 rows=244,795 width=426) (actual time=1,549.020..2,001.012 rows=195,149 loops=3)

  • Hash Cond: (ride_details.id = receipts.ride_detail_id)
  • Buffers: shared hit=113048, temp read=25320 written=25380
14. 583.814 583.814 ↑ 1.3 201,598 3 / 3

Parallel Seq Scan on ride_details (cost=0.00..101,026.72 rows=252,872 width=422) (actual time=0.010..583.814 rows=201,598 loops=3)

  • Buffers: shared hit=98498
15. 157.955 417.025 ↑ 1.3 195,701 3 / 3

Parallel Hash (cost=16,997.95..16,997.95 rows=244,795 width=8) (actual time=417.025..417.025 rows=195,701 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3936kB
  • Buffers: shared hit=14550, temp written=1788
16. 259.070 259.070 ↑ 1.3 195,701 3 / 3

Parallel Seq Scan on receipts (cost=0.00..16,997.95 rows=244,795 width=8) (actual time=0.009..259.070 rows=195,701 loops=3)

  • Buffers: shared hit=14550
17. 110.329 202.753 ↑ 1.2 88,145 3 / 3

Parallel Hash (cost=7,833.72..7,833.72 rows=110,172 width=56) (actual time=202.753..202.753 rows=88,145 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 3584kB
  • Buffers: shared hit=6732, temp written=2288
18. 92.424 92.424 ↑ 1.2 88,145 3 / 3

Parallel Seq Scan on patients (cost=0.00..7,833.72 rows=110,172 width=56) (actual time=0.012..92.424 rows=88,145 loops=3)

  • Buffers: shared hit=6732
19. 168.468 1,063.353 ↑ 1.3 146,411 3 / 3

Parallel Hash (cost=46,042.62..46,042.62 rows=183,255 width=125) (actual time=1,063.352..1,063.353 rows=146,411 loops=3)

  • Buckets: 32768 Batches: 32 Memory Usage: 1440kB
  • Buffers: shared hit=31653 dirtied=1, temp read=4462 written=8404
20. 406.092 894.885 ↑ 1.3 146,411 3 / 3

Parallel Hash Left Join (cost=9,950.02..46,042.62 rows=183,255 width=125) (actual time=674.600..894.885 rows=146,411 loops=3)

  • Hash Cond: (appointments.id = invoices.appt_id)
  • Buffers: shared hit=31653 dirtied=1, temp read=4462 written=4536
21. 251.346 251.346 ↑ 1.3 146,411 3 / 3

Parallel Seq Scan on appointments (cost=0.00..28,314.55 rows=183,255 width=117) (actual time=0.010..251.346 rows=146,411 loops=3)

  • Buffers: shared hit=26482 dirtied=1
22. 128.226 237.447 ↑ 1.3 139,374 3 / 3

Parallel Hash (cost=6,915.90..6,915.90 rows=174,490 width=12) (actual time=237.446..237.447 rows=139,374 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3520kB
  • Buffers: shared hit=5171, temp written=1476
23. 109.221 109.221 ↑ 1.3 139,374 3 / 3

Parallel Seq Scan on invoices (cost=0.00..6,915.90 rows=174,490 width=12) (actual time=0.009..109.221 rows=139,374 loops=3)

  • Buffers: shared hit=5171
24. 4.456 12.090 ↓ 1.0 5,026 3 / 3

Hash (cost=236.24..236.24 rows=5,024 width=91) (actual time=12.089..12.090 rows=5,026 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 642kB
  • Buffers: shared hit=558
25. 7.634 7.634 ↓ 1.0 5,026 3 / 3

Seq Scan on organisations (cost=0.00..236.24 rows=5,024 width=91) (actual time=0.016..7.634 rows=5,026 loops=3)

  • Buffers: shared hit=558
26. 0.268 0.516 ↓ 1.0 463 3 / 3

Hash (cost=11.54..11.54 rows=454 width=22) (actual time=0.515..0.516 rows=463 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
  • Buffers: shared hit=21
27. 0.248 0.248 ↓ 1.0 463 3 / 3

Seq Scan on market_segments (cost=0.00..11.54 rows=454 width=22) (actual time=0.012..0.248 rows=463 loops=3)

  • Buffers: shared hit=21
28. 25.285 53.080 ↑ 1.0 29,453 3 / 3

Hash (cost=1,186.66..1,186.66 rows=29,466 width=27) (actual time=53.079..53.080 rows=29,453 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1565kB
  • Buffers: shared hit=2676
29. 27.795 27.795 ↑ 1.0 29,453 3 / 3

Seq Scan on users (cost=0.00..1,186.66 rows=29,466 width=27) (actual time=0.009..27.795 rows=29,453 loops=3)

  • Buffers: shared hit=2676
30. 67.801 115.665 ↓ 1.0 64,124 3 / 3

Hash (cost=1,138.20..1,138.20 rows=64,120 width=38) (actual time=115.664..115.665 rows=64,124 loops=3)

  • Buckets: 65536 Batches: 2 Memory Usage: 1681kB
  • Buffers: shared hit=1491, temp written=294
31. 47.864 47.864 ↓ 1.0 64,124 3 / 3

Seq Scan on dynamite_claims (cost=0.00..1,138.20 rows=64,120 width=38) (actual time=0.014..47.864 rows=64,124 loops=3)

  • Buffers: shared hit=1491
32. 1.227 2.242 ↓ 1.0 2,166 3 / 3

Hash (cost=44.62..44.62 rows=2,162 width=31) (actual time=2.242..2.242 rows=2,166 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 173kB
  • Buffers: shared hit=69
33. 1.015 1.015 ↓ 1.0 2,166 3 / 3

Seq Scan on dynamite_claims_adjuster (cost=0.00..44.62 rows=2,162 width=31) (actual time=0.015..1.015 rows=2,166 loops=3)

  • Buffers: shared hit=69
34. 0.050 0.099 ↑ 1.0 77 3 / 3

Hash (cost=1.77..1.77 rows=77 width=29) (actual time=0.099..0.099 rows=77 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=3
35. 0.049 0.049 ↑ 1.0 77 3 / 3

Seq Scan on org_payers (cost=0.00..1.77 rows=77 width=29) (actual time=0.015..0.049 rows=77 loops=3)

  • Buffers: shared hit=3
36. 2.676 5.074 ↓ 1.0 5,026 3 / 3

Hash (cost=236.24..236.24 rows=5,024 width=22) (actual time=5.074..5.074 rows=5,026 loops=3)

  • Buckets: 8192 Batches: 1 Memory Usage: 339kB
  • Buffers: shared hit=558
37. 2.398 2.398 ↓ 1.0 5,026 3 / 3

Seq Scan on organisations req_org (cost=0.00..236.24 rows=5,024 width=22) (actual time=0.005..2.398 rows=5,026 loops=3)

  • Buffers: shared hit=558
38. 0.216 17.505 ↑ 1.7 594 1

Hash (cost=10.00..10.00 rows=1,000 width=64) (actual time=17.505..17.505 rows=594 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
39. 17.289 17.289 ↑ 1.7 594 1

Function Scan on pg_timezone_names (cost=0.00..10.00 rows=1,000 width=64) (actual time=17.139..17.289 rows=594 loops=1)

40.          

SubPlan (for Hash Left Join)

41. 1,170.814 8,195.698 ↓ 0.0 0 585,407

Sort (cost=48.85..48.86 rows=4 width=36) (actual time=0.014..0.014 rows=0 loops=585,407)

  • Sort Key: questions.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1339231 read=56
  • I/O Timings: read=4578.392
42. 532.451 7,024.884 ↓ 0.0 0 585,407

Nested Loop (cost=0.84..48.81 rows=4 width=36) (actual time=0.012..0.012 rows=0 loops=585,407)

  • Buffers: shared hit=1339231 read=56
  • I/O Timings: read=4578.392
43. 508.864 6,439.477 ↓ 0.0 0 585,407

Nested Loop (cost=0.56..47.53 rows=4 width=38) (actual time=0.011..0.011 rows=0 loops=585,407)

  • Buffers: shared hit=1259796 read=56
  • I/O Timings: read=4578.392
44. 5,268.663 5,268.663 ↓ 0.0 0 585,407

Index Scan using answers_appt_id_idx on answers (cost=0.29..18.33 rows=4 width=10) (actual time=0.009..0.009 rows=0 loops=585,407)

  • Index Cond: (appt_id = appointments.id)
  • Buffers: shared hit=1180370 read=47
  • I/O Timings: read=3981.003
45. 661.950 661.950 ↑ 1.0 1 26,478

Index Scan using question_pkey on questions (cost=0.28..7.29 rows=1 width=32) (actual time=0.025..0.025 rows=1 loops=26,478)

  • Index Cond: (id = answers.question_id)
  • Buffers: shared hit=79426 read=9
  • I/O Timings: read=597.390
46. 52.956 52.956 ↑ 1.0 1 26,478

Index Scan using questionnaire_pkey on questionnaires (cost=0.27..0.32 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=26,478)

  • Index Cond: (id = questions.questionnaire_id)
  • Buffers: shared hit=79435
Planning time : 9.464 ms
Execution time : 24,911.182 ms