explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HY49

Settings
# exclusive inclusive rows x rows loops node
1. 39.860 16,742.480 ↑ 4.5 7,123 1

Hash Left Join (cost=2,283.30..25,015,647.82 rows=32,239 width=1,085) (actual time=58.921..16,742.480 rows=7,123 loops=1)

  • Hash Cond: ((ride_details.appt_timezone)::text = pg_timezone_names.name)
2. 13.116 67.187 ↓ 1.1 7,123 1

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

3. 0.000 46.948 ↓ 1.1 7,123 1

Gather (cost=2,260.66..129,423.16 rows=6,448 width=897) (actual time=38.157..46.948 rows=7,123 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 3.604 258.912 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=1,260.66..127,778.36 rows=2,687 width=897) (actual time=27.891..258.912 rows=2,374 loops=3)

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

Hash Left Join (cost=1,109.24..127,618.88 rows=2,687 width=867) (actual time=26.861..254.298 rows=2,374 loops=3)

  • Hash Cond: (dynamite_claims.payer_id = org_payers.id)
6. 3.117 251.164 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=1,106.50..127,609.06 rows=2,687 width=846) (actual time=26.748..251.164 rows=2,374 loops=3)

  • Hash Cond: (dynamite_claims.claims_adjuster_id = dynamite_claims_adjuster.id)
7. 3.312 246.977 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=1,072.32..127,567.82 rows=2,687 width=809) (actual time=25.657..246.977 rows=2,374 loops=3)

  • Hash Cond: (appointments.dynamite_claims_id = dynamite_claims.id)
8. 6.399 221.636 ↑ 1.1 2,374 3 / 3

Nested Loop Left Join (cost=440.75..126,929.20 rows=2,687 width=776) (actual time=3.483..221.636 rows=2,374 loops=3)

9. 5.871 205.740 ↑ 1.1 2,374 3 / 3

Nested Loop Left Join (cost=440.32..116,286.28 rows=2,662 width=760) (actual time=3.450..205.740 rows=2,374 loops=3)

10. 3.331 187.997 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=439.90..115,003.80 rows=2,662 width=734) (actual time=3.405..187.997 rows=2,374 loops=3)

  • Hash Cond: (organisations.market_segment_id = market_segments.id)
11. 5.292 184.647 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=438.79..114,989.78 rows=2,662 width=720) (actual time=3.369..184.647 rows=2,374 loops=3)

  • Hash Cond: (ride_details.org_id = organisations.id)
12. 5.336 177.199 ↑ 1.1 2,374 3 / 3

Nested Loop Left Join (cost=287.37..114,831.34 rows=2,662 width=637) (actual time=1.121..177.199 rows=2,374 loops=3)

13. 5.253 159.991 ↑ 1.1 2,374 3 / 3

Nested Loop (cost=286.95..113,544.58 rows=2,662 width=614) (actual time=1.067..159.991 rows=2,374 loops=3)

14. 8.060 140.492 ↑ 1.1 2,374 3 / 3

Nested Loop (cost=286.52..110,924.69 rows=2,673 width=520) (actual time=1.020..140.492 rows=2,374 loops=3)

15. 12.768 13.416 ↑ 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.872..13.416 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
16. 0.648 0.648 ↓ 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=1.943..1.944 rows=15,195 loops=1)

  • Index Cond: ((invoice_generation_time >= 1,577,836,800) AND (invoice_generation_time <= 1,579,132,799))
17. 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
18. 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)
19. 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)
20. 0.671 2.156 ↓ 1.0 954 3 / 3

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

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

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

22. 0.007 0.019 ↑ 1.0 5 3 / 3

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

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

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

24. 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)
25. 9.497 9.497 ↑ 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.004..0.004 rows=1 loops=7,123)

  • Index Cond: (appt_id = appointments.id)
26. 10.666 22.029 ↑ 1.0 20,111 3 / 3

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

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

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

28. 0.565 1.070 ↑ 1.0 946 3 / 3

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

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

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

30. 0.047 0.094 ↑ 1.0 77 3 / 3

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

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

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

32. 0.504 1.010 ↓ 1.0 954 3 / 3

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

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

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

34. 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)
35. 0.225 17.474 ↑ 1.7 594 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 50kB
36. 17.249 17.249 ↑ 1.7 594 1

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

37.          

SubPlan (for Hash Left Join)

38. 0.000 16,617.959 ↓ 0.0 0 7,123

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

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

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

40. 16,603.713 16,603.713 ↓ 0.0 0 7,123

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

  • Filter: (appt_id = appointments.id)
  • Rows Removed by Filter: 29,949
41. 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)
42. 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 : 14.449 ms
Execution time : 16,745.702 ms