explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tJcJ

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

Sort (cost=24,888,203.84..24,888,283.86 rows=32,009 width=1,089) (actual time=17,437.394..17,442.225 rows=7,123 loops=1)

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

Hash Left Join (cost=1,997.21..24,870,489.08 rows=32,009 width=1,089) (actual time=62.991..17,397.858 rows=7,123 loops=1)

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

Nested Loop Left Join (cost=1,974.70..162,422.18 rows=6,402 width=902) (actual time=33.402..62.631 rows=7,123 loops=1)

4. 0.000 41.790 ↓ 1.1 7,123 1

Gather (cost=1,974.56..161,328.32 rows=6,402 width=901) (actual time=33.390..41.790 rows=7,123 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 3.106 400.282 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=974.56..159,688.12 rows=2,668 width=901) (actual time=33.034..400.282 rows=2,374 loops=3)

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

Hash Left Join (cost=823.14..159,528.69 rows=2,668 width=871) (actual time=32.156..396.317 rows=2,374 loops=3)

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

Hash Left Join (cost=820.41..159,518.92 rows=2,668 width=850) (actual time=32.056..393.511 rows=2,374 loops=3)

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

Hash Left Join (cost=786.23..159,477.73 rows=2,668 width=813) (actual time=31.149..389.971 rows=2,374 loops=3)

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

Nested Loop Left Join (cost=154.65..158,839.16 rows=2,668 width=780) (actual time=8.041..364.141 rows=2,374 loops=3)

10. 4.030 349.929 ↑ 1.1 2,374 3 / 3

Nested Loop Left Join (cost=154.23..148,228.56 rows=2,662 width=764) (actual time=8.000..349.929 rows=2,374 loops=3)

11. 3.232 334.027 ↑ 1.1 2,374 3 / 3

Hash Left Join (cost=153.81..146,946.10 rows=2,662 width=738) (actual time=7.951..334.027 rows=2,374 loops=3)

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

Hash Left Join (cost=152.70..146,932.08 rows=2,662 width=724) (actual time=7.913..330.775 rows=2,374 loops=3)

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

Nested Loop Left Join (cost=1.27..146,773.64 rows=2,662 width=641) (actual time=5.918..325.004 rows=2,374 loops=3)

14. 5.634 309.683 ↑ 1.1 2,374 3 / 3

Nested Loop (cost=0.85..145,486.87 rows=2,662 width=618) (actual time=5.867..309.683 rows=2,374 loops=3)

15. 11.135 292.177 ↑ 1.1 2,374 3 / 3

Nested Loop (cost=0.43..142,867.01 rows=2,673 width=524) (actual time=5.826..292.177 rows=2,374 loops=3)

16. 181.862 181.862 ↑ 1.1 4,959 3 / 3

Parallel Seq Scan on appointments (cost=0.00..63,490.47 rows=5,370 width=114) (actual time=2.115..181.862 rows=4,959 loops=3)

  • Filter: (invoice_generated AND (invoice_generation_time >= 1,577,836,800) AND (invoice_generation_time <= 1,579,132,799))
  • Rows Removed by Filter: 301,056
17. 99.180 99.180 ↓ 0.0 0 14,877 / 3

Index Scan using idx_appt_id_ride_detail on ride_details (cost=0.43..14.77 rows=1 width=410) (actual time=0.018..0.020 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. 11.872 11.872 ↑ 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.005..0.005 rows=1 loops=7,123)

  • Index Cond: (ride_detail_id = ride_details.id)
19. 9.497 9.497 ↑ 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.004..0.004 rows=1 loops=7,123)

  • Index Cond: (ride_details.patient_id = id)
20. 0.674 1.889 ↓ 1.0 954 3 / 3

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

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

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

22. 0.008 0.020 ↑ 1.0 5 3 / 3

Hash (cost=1.05..1.05 rows=5 width=22) (actual time=0.020..0.020 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.010..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..3.99 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=7,123)

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

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,012kB
27. 12.017 12.017 ↑ 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..12.017 rows=20,111 loops=3)

28. 0.470 0.888 ↑ 1.0 946 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 82kB
29. 0.418 0.418 ↑ 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.418 rows=946 loops=3)

30. 0.039 0.081 ↑ 1.0 77 3 / 3

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

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

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

32. 0.437 0.859 ↓ 1.0 954 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 72kB
33. 0.422 0.422 ↓ 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.422 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.359 23.573 ↑ 1.7 594 1

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

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

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

37.          

SubPlan (for Hash Left Join)

38. 14.246 17,273.275 ↓ 0.0 0 7,123

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

  • Sort Key: questions.id
  • Sort Method: quicksort Memory: 25kB
39. 0.000 17,259.029 ↓ 0.0 0 7,123

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

40. 14.246 17,259.029 ↓ 0.0 0 7,123

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

41. 17,244.783 17,244.783 ↓ 0.0 0 7,123

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

  • Filter: (appt_id = appointments.id)
  • Rows Removed by Filter: 29,947
42. 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)
43. 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.987 ms
Execution time : 17,448.932 ms