explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tfAa

Settings
# exclusive inclusive rows x rows loops node
1. 47.504 17,052.264 ↑ 4.5 7,123 1

Sort (cost=25,033,973.54..25,034,054.14 rows=32,239 width=1,089) (actual time=17,047.403..17,052.264 rows=7,123 loops=1)

  • Sort Key: ride_details.appt_id, ride_details.id
  • Sort Method: external merge Disk: 5,152kB
2. 43.414 17,004.760 ↑ 4.5 7,123 1

Hash Left Join (cost=2,283.30..25,016,131.40 rows=32,239 width=1,089) (actual time=66.279..17,004.760 rows=7,123 loops=1)

  • Hash Cond: ((ride_details.appt_timezone)::text = pg_timezone_names.name)
3. 14.600 69.251 ↓ 1.1 7,123 1

Nested Loop Left Join (cost=2,260.80..130,525.37 rows=6,448 width=902) (actual time=38.692..69.251 rows=7,123 loops=1)

4. 0.000 47.528 ↓ 1.1 7,123 1

Gather (cost=2,260.66..129,423.16 rows=6,448 width=901) (actual time=38.673..47.528 rows=7,123 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 3.794 268.355 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=1,260.66..127,778.36 rows=2,687 width=901) (actual time=33.953..268.355 rows=2,374 loops=3)

  • Hash Cond: (((appointments.additional_details ->> 'requesting_organization'::text))::integer = req_org.id)
6. 3.418 263.298 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=1,109.24..127,618.88 rows=2,687 width=871) (actual time=32.668..263.298 rows=2,374 loops=3)

  • Hash Cond: (dynamite_claims.payer_id = org_payers.id)
7. 3.017 259.770 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=1,106.50..127,609.06 rows=2,687 width=850) (actual time=32.537..259.770 rows=2,374 loops=3)

  • Hash Cond: (dynamite_claims.claims_adjuster_id = dynamite_claims_adjuster.id)
8. 3.339 255.457 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=1,072.32..127,567.82 rows=2,687 width=813) (actual time=31.217..255.457 rows=2,374 loops=3)

  • Hash Cond: (appointments.dynamite_claims_id = dynamite_claims.id)
9. 4.560 225.033 ↑ 1.1 2,374 3 / 3

Nested Loop Left Join (cost=440.75..126,929.20 rows=2,687 width=780) (actual time=3.972..225.033 rows=2,374 loops=3)

10. 5.882 208.601 ↑ 1.1 2,374 3 / 3

Nested Loop Left Join (cost=440.32..116,286.28 rows=2,662 width=764) (actual time=3.933..208.601 rows=2,374 loops=3)

11. 3.431 190.847 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=439.90..115,003.80 rows=2,662 width=738) (actual time=3.883..190.847 rows=2,374 loops=3)

  • Hash Cond: (organisations.market_segment_id = market_segments.id)
12. 4.473 187.393 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=438.79..114,989.78 rows=2,662 width=724) (actual time=3.838..187.393 rows=2,374 loops=3)

  • Hash Cond: (ride_details.org_id = organisations.id)
13. 6.106 180.401 ↑ 1.1 2,374 3 / 3

Nested Loop Left Join (cost=287.37..114,831.34 rows=2,662 width=641) (actual time=1.223..180.401 rows=2,374 loops=3)

14. 6.444 162.423 ↑ 1.1 2,374 3 / 3

Nested Loop (cost=286.95..113,544.58 rows=2,662 width=618) (actual time=1.161..162.423 rows=2,374 loops=3)

15. 8.929 141.733 ↑ 1.1 2,374 3 / 3

Nested Loop (cost=286.52..110,924.69 rows=2,673 width=524) (actual time=1.109..141.733 rows=2,374 loops=3)

16. 13.057 13.788 ↑ 1.1 4,959 3 / 3

Parallel Bitmap Heap Scan on appointments (cost=286.09..32,040.33 rows=5,333 width=114) (actual time=0.962..13.788 rows=4,959 loops=3)

  • Recheck Cond: ((invoice_generation_time >= 1,577,836,800) AND (invoice_generation_time <= 1,579,132,799))
  • Filter: invoice_generated
  • Rows Removed by Filter: 106
  • Heap Blocks: exact=6
17. 0.731 0.731 ↓ 1.1 15,195 1 / 3

Bitmap Index Scan on idx_appt_invoice_generation_time (cost=0.00..282.90 rows=13,447 width=0) (actual time=2.192..2.192 rows=15,195 loops=1)

  • Index Cond: ((invoice_generation_time >= 1,577,836,800) AND (invoice_generation_time <= 1,579,132,799))
18. 119.016 119.016 ↓ 0.0 0 14,877 / 3

Index Scan using idx_appt_id_ride_detail on ride_details (cost=0.43..14.78 rows=1 width=410) (actual time=0.022..0.024 rows=0 loops=14,877)

  • Index Cond: (appt_id = appointments.id)
  • Filter: ((NOT is_pr_ride) AND ((ride_category)::text = 'completed'::text) AND (org_id = ANY ('{573,147,148,584,1672,736,149,551,150,620,1808,713,114,231,655,54,567,730,663,676,115,672,232,677,233,67,750,563,675,127,151,641,592,690,600,128,194,195,196,644,656,55,152,234,221,582,350,734,83,720,217,1719,198,709,753,751,154,737,442,362,698,267,235,100,116,268,410,236,586,363,1316,681,237,269,101,199,408,570,238,155,239,56,240,310,241,404,342,69,1724,423,156,635,667,717,649,157,412,638,84,729,555,433,434,652,85,623,117,200,699,590,722,585,587,270,671,308,158,653,602,271,631,390,721,580,413,242,130,613,131,726,550,745,118,243,601,355,356,357,129,659,272,574,102,201,159,614,132,688,1720,103,665,273,629,633,244,99,632,1657,160,274,628,630,66,57,664,714,428,364,365,443,311,366,300,301,399,642,696,612,727,740,566,97,98,609,738,110,728,637,682,405,596,161,202,758,275,625,406,702,245,568,349,747,693,359,748,545,689,544,58,162,163,576,554,133,79,78,218,424,598,589,409,276,164,134,277,278,59,246,669,645,704,739,549,435,577,247,248,599,165,203,279,166,280,431,718,414,135,416,281,707,1699,249,610,697,1819,617,593,1651,204,673,205,634,119,167,749,755,711,719,206,766,168,207,591,208,556,547,250,169,170,171,87,583,686,657,86,251,209,562,579,283,60,284,285,286,756,210,104,757,172,173,252,253,72,679,746,575,288,1745,351,691,558,594,678,425,680,670,626,624,743,88,415,174,105,352,136,643,660,700,175,560,692,254,622,358,68,216,1807,120,754,289,255,581,219,353,177,137,572,426,142,73,111,742,220,211,606,121,604,603,546,256,1760,735,290,71,178,441,651,647,427,391,392,141,724,752,74,1695,674,75,542,354,561,440,212,543,687,1696,432,605,70,715,578,648,213,621,703,588,89,257,214,710,616,639,291,552,61,258,668,287,706,215,292,293,90,259,312,417,140,50,179,378,732,180,62,1562,684,595,565,553,723,138,112,260,705,683,182,646,636,360,685,361,63,418,571,91,64,65,153,564,183,294,1568,261,419,420,421,557,731,654,569,701,262,139,716,662,712,615,708,339,184,627,263,185,186,422,607,264,302,122,92,106,295,666,388,296,107,123,187,265,188,189,266,733,650,618,611,559,548,190}'::integer[])))
  • Rows Removed by Filter: 1
19. 14.246 14.246 ↑ 1.0 1 7,123 / 3

Index Scan using unique_receipts_id on receipts (cost=0.43..0.98 rows=1 width=98) (actual time=0.006..0.006 rows=1 loops=7,123)

  • Index Cond: (ride_detail_id = ride_details.id)
20. 11.872 11.872 ↑ 1.0 1 7,123 / 3

Index Scan using patients_pkey on patients (cost=0.42..0.48 rows=1 width=31) (actual time=0.005..0.005 rows=1 loops=7,123)

  • Index Cond: (ride_details.patient_id = id)
21. 0.807 2.519 ↓ 1.0 954 3 / 3

Hash (cost=139.52..139.52 rows=952 width=87) (actual time=2.518..2.519 rows=954 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 120kB
22. 1.712 1.712 ↓ 1.0 954 3 / 3

Seq Scan on organisations (cost=0.00..139.52 rows=952 width=87) (actual time=0.017..1.712 rows=954 loops=3)

23. 0.008 0.023 ↑ 1.0 5 3 / 3

Hash (cost=1.05..1.05 rows=5 width=22) (actual time=0.023..0.023 rows=5 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
24. 0.015 0.015 ↑ 1.0 5 3 / 3

Seq Scan on market_segments (cost=0.00..1.05 rows=5 width=22) (actual time=0.011..0.015 rows=5 loops=3)

25. 11.872 11.872 ↑ 1.0 1 7,123 / 3

Index Scan using users_pkey on users (cost=0.42..0.48 rows=1 width=30) (actual time=0.005..0.005 rows=1 loops=7,123)

  • Index Cond: (ride_details.ride_requester_id = id)
26. 11.872 11.872 ↑ 1.0 1 7,123 / 3

Index Scan using appt_id on invoices (cost=0.42..4.00 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=7,123)

  • Index Cond: (appt_id = appointments.id)
27. 12.956 27.085 ↑ 1.0 20,111 3 / 3

Hash (cost=376.81..376.81 rows=20,381 width=41) (actual time=27.085..27.085 rows=20,111 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,012kB
28. 14.129 14.129 ↑ 1.0 20,111 3 / 3

Seq Scan on dynamite_claims (cost=0.00..376.81 rows=20,381 width=41) (actual time=0.013..14.129 rows=20,111 loops=3)

29. 0.690 1.296 ↑ 1.0 946 3 / 3

Hash (cost=21.86..21.86 rows=986 width=45) (actual time=1.296..1.296 rows=946 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 82kB
30. 0.606 0.606 ↑ 1.0 946 3 / 3

Seq Scan on dynamite_claims_adjuster (cost=0.00..21.86 rows=986 width=45) (actual time=0.015..0.606 rows=946 loops=3)

31. 0.055 0.110 ↑ 1.0 77 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
32. 0.055 0.055 ↑ 1.0 77 3 / 3

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

33. 0.628 1.263 ↓ 1.0 954 3 / 3

Hash (cost=139.52..139.52 rows=952 width=34) (actual time=1.262..1.263 rows=954 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 72kB
34. 0.635 0.635 ↓ 1.0 954 3 / 3

Seq Scan on organisations req_org (cost=0.00..139.52 rows=952 width=34) (actual time=0.004..0.635 rows=954 loops=3)

35. 7.123 7.123 ↓ 0.0 0 7,123

Index Scan using invitations_pkey on invitations (cost=0.14..0.17 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=7,123)

  • Index Cond: (id = appointments.invitation_id)
36. 0.224 24.831 ↑ 1.7 594 1

Hash (cost=10.00..10.00 rows=1,000 width=80) (actual time=24.830..24.831 rows=594 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 50kB
37. 24.607 24.607 ↑ 1.7 594 1

Function Scan on pg_timezone_names (cost=0.00..10.00 rows=1,000 width=80) (actual time=24.457..24.607 rows=594 loops=1)

38.          

SubPlan (for Hash Left Join)

39. 14.246 16,867.264 ↓ 0.0 0 7,123

Sort (cost=771.85..771.85 rows=2 width=36) (actual time=2.368..2.368 rows=0 loops=7,123)

  • Sort Key: questions.id
  • Sort Method: quicksort Memory: 25kB
40. 7.123 16,853.018 ↓ 0.0 0 7,123

Nested Loop (cost=0.55..771.84 rows=2 width=36) (actual time=2.366..2.366 rows=0 loops=7,123)

41. 7.123 16,845.895 ↓ 0.0 0 7,123

Nested Loop (cost=0.28..771.19 rows=2 width=74) (actual time=2.365..2.365 rows=0 loops=7,123)

42. 16,838.772 16,838.772 ↓ 0.0 0 7,123

Seq Scan on answers (cost=0.00..754.59 rows=2 width=22) (actual time=2.364..2.364 rows=0 loops=7,123)

  • Filter: (appt_id = appointments.id)
  • Rows Removed by Filter: 29,949
43. 0.000 0.000 ↓ 0.0 0

Index Scan using question_pkey on questions (cost=0.28..8.29 rows=1 width=56) (never executed)

  • Index Cond: (id = answers.question_id)
44. 0.000 0.000 ↓ 0.0 0

Index Scan using questionnaire_pkey on questionnaires (cost=0.28..0.32 rows=1 width=10) (never executed)

  • Index Cond: (id = questions.questionnaire_id)
Planning time : 15.345 ms
Execution time : 17,059.223 ms