explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SsuL3

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 132,159.381 ↑ 1.0 1 1

INFO [2020-09-11 16:07:12] "GET /api/v1/versions/my_latest HTTP/1.1" 200 2INFO [2020-09-11 16:07:12] "GET /api/v1/account_logo HTTP/1.1" 200 9647INFO [2020-09-11 16:07:12] "GET /api/v1/person_types HTTP/1.1" 200 26INFO [2020-09-11 16:07:12] "GET /api/v1/users/me HTTP/1.1" 200 9066INFO [2020-09-11 16:07:12] "GET /api/v1/account_logo?oncoshare.localhost:4000 HTTP/1.1" 200 9647get_required_permissionsINFO [2020-09-11 16:07:12] "OPTIONS /api/v1/permissions HTTP/1.1" 200 0INFO [2020-09-11 16:07:13] "POST /api/v1/permissions HTTP/1.1" 200 40Aggregate (cost=6,366.75..6,366.76 rows=1 width=0) (actual time=132,159.381..132,159.381 rows=1 loops=1)

2. 32,018.603 132,159.374 ↓ 15.7 47 1

HashAggregate (cost=6,366.69..6,366.72 rows=3 width=127) (actual time=132,159.365..132,159.374 rows=47 loops=1)

  • Group Key: patients_partner.id, patients_partner.deleted, patients_partner.name, patients_partner.main_account_id, patients_partner.data_account_id, patients_person.partner_ptr_id, patients_person.created_at, patients_person.updated_at, patients_person.updated_by_id, patients_person.created_by_id, patients_person.firstname, patients_person.lastname, patients_person.birthdate, patients_person.deathdate, patients_person.email, patients_person.phone_number, patients_person.sex_id, patients_person.country_id, patients_person.user_id, patients_person.title_id
3. 25,827.070 100,140.771 ↓ 12,329,894.7 36,989,684 1

Nested Loop Left Join (cost=4,329.00..6,366.54 rows=3 width=127) (actual time=2,651.959..100,140.771 rows=36,989,684 loops=1)

  • Filter: (((t27.distribution_company_id = 27,731) AND (t27.ended_at IS NULL) AND (t27.started_at IS NOT NULL)) OR (patients_person.user_id = 13,463) OR ((t30.distribution_company_id = 27,731) AND (t30.ended_at IS NULL) AND (t30.started_at IS NOT NULL)) OR ((t34.distribution_company_id = 27,731) AND (t34.ended_at IS NULL) AND (t34.started_at IS NOT NULL)))
  • Rows Removed by Filter: 2,411,226
4. 6,774.864 34,912.791 ↓ 2,188,939.4 39,400,910 1

Nested Loop Left Join (cost=4,328.72..6,360.60 rows=18 width=171) (actual time=2,651.952..34,912.791 rows=39,400,910 loops=1)

5. 1,296.602 12,507.677 ↓ 347,338.9 3,126,050 1

Nested Loop Left Join (cost=4,328.43..6,354.44 rows=9 width=171) (actual time=2,651.949..12,507.677 rows=3,126,050 loops=1)

6. 1,384.593 11,211.075 ↓ 347,338.9 3,126,050 1

Nested Loop Left Join (cost=4,328.15..6,351.56 rows=9 width=155) (actual time=2,651.947..11,211.075 rows=3,126,050 loops=1)

7. 1,357.687 9,826.482 ↓ 347,338.9 3,126,050 1

Nested Loop Left Join (cost=4,327.86..6,348.19 rows=9 width=155) (actual time=2,651.945..9,826.482 rows=3,126,050 loops=1)

8. 529.907 8,468.795 ↓ 347,338.9 3,126,050 1

Nested Loop Left Join (cost=4,327.57..6,344.75 rows=9 width=155) (actual time=2,651.944..8,468.795 rows=3,126,050 loops=1)

9. 1,327.014 6,017.322 ↓ 71,169.1 640,522 1

Nested Loop Left Join (cost=4,327.29..6,341.75 rows=9 width=135) (actual time=2,651.941..6,017.322 rows=640,522 loops=1)

  • Filter: ((upper((patients_person.firstname)::text) ~~ '%.COM%'::text) OR (upper((patients_person.lastname)::text) ~~ '%.COM%'::text) OR (upper((patients_person.email)::text) ~~ '%.COM%'::text) OR (upper((t24.name)::text) ~~ '%.COM%'::text) OR (upper((t25.readable_id)::text) ~~ '%.COM%'::text) OR (upper((t26.readable_id)::text) ~~ '%.COM%'::text))
10. 53.838 4,161.758 ↓ 429.0 48,050 1

Nested Loop Left Join (cost=4,327.00..6,245.86 rows=112 width=158) (actual time=2,651.933..4,161.758 rows=48,050 loops=1)

11. 46.508 4,107.920 ↓ 429.0 48,050 1

Nested Loop Left Join (cost=4,326.72..6,203.13 rows=112 width=148) (actual time=2,651.932..4,107.920 rows=48,050 loops=1)

12. 60.569 3,917.262 ↓ 429.0 48,050 1

Hash Left Join (cost=4,326.43..5,730.17 rows=112 width=139) (actual time=2,651.927..3,917.262 rows=48,050 loops=1)

  • Hash Cond: (t21.distribution_company_id = t22.company_ptr_id)
13. 32.091 3,856.608 ↓ 429.0 48,050 1

Nested Loop Left Join (cost=4,316.27..5,718.48 rows=112 width=139) (actual time=2,651.832..3,856.608 rows=48,050 loops=1)

14. 1,116.182 3,781.005 ↓ 97.1 10,878 1

Hash Left Join (cost=4,315.99..5,681.13 rows=112 width=135) (actual time=2,651.830..3,781.005 rows=10,878 loops=1)

  • Hash Cond: (t18.id = t19.sample_set_id)
  • Filter: (((t11.distribution_company_id = 27,731) AND (t11.ended_at IS NULL) AND (t11.started_at IS NOT NULL)) OR (patients_person.user_id = 13,463) OR ((patients_samplesethandling.distribution_company_id = 27,731) AND (patients_samplesethandling.ended_at IS NULL) AND (patients_samplesethandling.started_at IS NOT NULL)) OR ((t19.distribution_company_id = 27,731) AND (t19.ended_at IS NULL) AND (t19.started_at IS NOT NULL)))
  • Rows Removed by Filter: 5,186,854
15. 921.232 2,662.374 ↓ 7,911.3 5,197,732 1

Hash Left Join (cost=4,174.01..5,531.70 rows=657 width=179) (actual time=67.655..2,662.374 rows=5,197,732 loops=1)

  • Hash Cond: (analyses_sampleset.id = patients_samplesethandling.sample_set_id)
16. 644.398 1,739.220 ↓ 7,876.2 5,174,636 1

Nested Loop Left Join (cost=4,032.03..5,383.95 rows=657 width=163) (actual time=65.696..1,739.220 rows=5,174,636 loops=1)

17. 7.754 163.752 ↓ 40.2 13,301 1

Nested Loop Left Join (cost=4,031.74..5,157.37 rows=331 width=159) (actual time=65.682..163.752 rows=13,301 loops=1)

18. 8.207 155.998 ↓ 40.9 13,301 1

Nested Loop Left Join (cost=4,031.45..5,035.70 rows=325 width=159) (actual time=65.680..155.998 rows=13,301 loops=1)

19. 4.191 147.791 ↓ 40.9 13,301 1

Nested Loop Left Join (cost=4,031.17..4,911.70 rows=325 width=155) (actual time=65.678..147.791 rows=13,301 loops=1)

20. 45.674 122.042 ↓ 33.2 10,779 1

Hash Right Join (cost=4,030.88..4,803.32 rows=325 width=135) (actual time=65.668..122.042 rows=10,779 loops=1)

  • Hash Cond: (patients_oncologist.person_ptr_id = patients_person.partner_ptr_id)
  • Filter: ((upper((patients_person.firstname)::text) ~~ '%.COM%'::text) OR (upper((patients_person.lastname)::text) ~~ '%.COM%'::text) OR (upper((patients_person.email)::text) ~~ '%.COM%'::text) OR (upper((t6.name)::text) ~~ '%.COM%'::text) OR (upper((patients_patientcase.readable_id)::text) ~~ '%.COM%'::text) OR (upper((t10.readable_id)::text) ~~ '%.COM%'::text))
  • Rows Removed by Filter: 8,347
21. 8.340 12.974 ↓ 1.4 18,848 1

Hash Right Join (cost=201.94..848.11 rows=13,171 width=14) (actual time=2.224..12.974 rows=18,848 loops=1)

  • Hash Cond: (t10.oncologist_id = patients_oncologist.person_ptr_id)
22. 2.469 2.469 ↑ 1.0 18,797 1

Seq Scan on patients_patientcase t10 (cost=0.00..443.97 rows=18,797 width=14) (actual time=0.004..2.469 rows=18,797 loops=1)

23. 1.260 2.165 ↑ 1.0 7,464 1

Hash (cost=108.64..108.64 rows=7,464 width=4) (actual time=2.165..2.165 rows=7,464 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 327kB
24. 0.905 0.905 ↑ 1.0 7,464 1

Seq Scan on patients_oncologist (cost=0.00..108.64 rows=7,464 width=4) (actual time=0.010..0.905 rows=7,464 loops=1)

25. 4.187 63.394 ↓ 2.0 7,990 1

Hash (cost=3,780.04..3,780.04 rows=3,912 width=154) (actual time=63.394..63.394 rows=7,990 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,238kB
26. 1.935 59.207 ↓ 2.0 7,990 1

Hash Left Join (cost=3,138.70..3,780.04 rows=3,912 width=154) (actual time=45.859..59.207 rows=7,990 loops=1)

  • Hash Cond: (patients_personhandling.distribution_company_id = patients_distributioncompany.company_ptr_id)
27. 7.988 56.957 ↓ 2.0 7,990 1

Hash Join (cost=2,642.92..3,230.47 rows=3,912 width=145) (actual time=45.537..56.957 rows=7,990 loops=1)

  • Hash Cond: (patients_partner.id = patients_person.partner_ptr_id)
28. 3.466 3.466 ↑ 1.0 26,430 1

Seq Scan on patients_partner (cost=0.00..449.32 rows=26,430 width=26) (actual time=0.010..3.466 rows=26,430 loops=1)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 602
29. 3.115 45.503 ↓ 2.1 8,206 1

Hash (cost=2,592.90..2,592.90 rows=4,001 width=119) (actual time=45.503..45.503 rows=8,206 loops=1)

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,065kB
30. 5.854 42.388 ↓ 2.1 8,206 1

Hash Right Join (cost=2,038.47..2,592.90 rows=4,001 width=119) (actual time=40.583..42.388 rows=8,206 loops=1)

  • Hash Cond: (patients_patientcase.patient_id = patients_patient.person_ptr_id)
31. 2.301 2.301 ↑ 1.0 18,797 1

Seq Scan on patients_patientcase (cost=0.00..443.97 rows=18,797 width=14) (actual time=0.005..2.301 rows=18,797 loops=1)

32. 2.753 34.233 ↓ 2.1 8,206 1

Hash (cost=1,988.46..1,988.46 rows=4,001 width=109) (actual time=34.233..34.233 rows=8,206 loops=1)

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,065kB
33. 3.676 31.480 ↓ 2.1 8,206 1

Hash Right Join (cost=1,813.41..1,988.46 rows=4,001 width=109) (actual time=27.198..31.480 rows=8,206 loops=1)

  • Hash Cond: (patients_personhandling.person_id = patients_person.partner_ptr_id)
34. 0.629 0.629 ↑ 1.0 8,073 1

Seq Scan on patients_personhandling (cost=0.00..132.73 rows=8,073 width=8) (actual time=0.005..0.629 rows=8,073 loops=1)

35. 3.438 27.175 ↓ 2.0 8,052 1

Hash (cost=1,763.40..1,763.40 rows=4,001 width=105) (actual time=27.175..27.175 rows=8,052 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 971kB
36. 8.197 23.737 ↓ 2.0 8,052 1

Hash Anti Join (cost=948.51..1,763.40 rows=4,001 width=105) (actual time=5.914..23.737 rows=8,052 loops=1)

  • Hash Cond: (patients_person.partner_ptr_id = patients_patient.person_ptr_id)
37. 9.823 10.088 ↓ 2.0 26,683 1

Seq Scan on patients_person (cost=446.17..1,176.34 rows=13,124 width=101) (actual time=0.359..10.088 rows=26,683 loops=1)

  • Filter: (((NOT (hashed SubPlan 1)) OR (user_id IS NULL)) AND ((NOT (hashed SubPlan 2)) OR (user_id IS NULL)))
  • Rows Removed by Filter: 128
38.          

SubPlan (for Seq Scan)

39. 0.018 0.157 ↓ 4.4 128 1

Nested Loop (cost=5.19..223.01 rows=29 width=4) (actual time=0.046..0.157 rows=128 loops=1)

40. 0.010 0.010 ↑ 1.0 1 1

Index Scan using auth_group_name_a6ea08ec_like on auth_group u3 (cost=0.28..8.30 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((name)::text = 'SUPER_ADMINS'::text)
41. 0.106 0.129 ↓ 1.6 128 1

Bitmap Heap Scan on authentication_user_groups u2 (cost=4.91..213.92 rows=80 width=8) (actual time=0.034..0.129 rows=128 loops=1)

  • Recheck Cond: (group_id = u3.id)
  • Heap Blocks: exact=80
42. 0.023 0.023 ↓ 1.6 128 1

Bitmap Index Scan on authentication_user_groups_0e939a4f (cost=0.00..4.89 rows=80 width=0) (actual time=0.023..0.023 rows=128 loops=1)

  • Index Cond: (group_id = u3.id)
43. 0.017 0.108 ↓ 4.4 128 1

Nested Loop (cost=5.19..223.01 rows=29 width=4) (actual time=0.033..0.108 rows=128 loops=1)

44. 0.004 0.004 ↑ 1.0 1 1

Index Scan using auth_group_name_a6ea08ec_like on auth_group u3_1 (cost=0.28..8.30 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)

  • Index Cond: ((name)::text = 'SUPER_ADMINS'::text)
45. 0.066 0.087 ↓ 1.6 128 1

Bitmap Heap Scan on authentication_user_groups u2_1 (cost=4.91..213.92 rows=80 width=8) (actual time=0.029..0.087 rows=128 loops=1)

  • Recheck Cond: (group_id = u3_1.id)
  • Heap Blocks: exact=80
46. 0.021 0.021 ↓ 1.6 128 1

Bitmap Index Scan on authentication_user_groups_0e939a4f (cost=0.00..4.89 rows=80 width=0) (actual time=0.021..0.021 rows=128 loops=1)

  • Index Cond: (group_id = u3_1.id)
47. 3.514 5.452 ↑ 1.0 18,637 1

Hash (cost=269.37..269.37 rows=18,637 width=4) (actual time=5.452..5.452 rows=18,637 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 912kB
48. 1.938 1.938 ↑ 1.0 18,637 1

Seq Scan on patients_patient (cost=0.00..269.37 rows=18,637 width=4) (actual time=0.005..1.938 rows=18,637 loops=1)

49. 0.016 0.315 ↑ 1.0 115 1

Hash (cost=494.35..494.35 rows=115 width=17) (actual time=0.315..0.315 rows=115 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
50. 0.097 0.299 ↑ 1.0 115 1

Nested Loop Left Join (cost=3.88..494.35 rows=115 width=17) (actual time=0.071..0.299 rows=115 loops=1)

51. 0.042 0.087 ↑ 1.0 115 1

Hash Right Join (cost=3.59..8.72 rows=115 width=8) (actual time=0.055..0.087 rows=115 loops=1)

  • Hash Cond: (patients_company.partner_ptr_id = patients_distributioncompany.company_ptr_id)
52. 0.013 0.013 ↑ 1.0 217 1

Seq Scan on patients_company (cost=0.00..3.17 rows=217 width=4) (actual time=0.005..0.013 rows=217 loops=1)

53. 0.016 0.032 ↑ 1.0 115 1

Hash (cost=2.15..2.15 rows=115 width=4) (actual time=0.032..0.032 rows=115 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
54. 0.016 0.016 ↑ 1.0 115 1

Seq Scan on patients_distributioncompany (cost=0.00..2.15 rows=115 width=4) (actual time=0.006..0.016 rows=115 loops=1)

55. 0.115 0.115 ↑ 1.0 1 115

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t6 (cost=0.29..4.21 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=115)

  • Index Cond: (patients_company.partner_ptr_id = id)
56. 21.558 21.558 ↑ 1.0 1 10,779

Index Scan using patients_personhandling_a8452ca7 on patients_personhandling t11 (cost=0.28..0.32 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=10,779)

  • Index Cond: (patients_person.partner_ptr_id = person_id)
57. 0.000 0.000 ↓ 0.0 0 13,301

Index Scan using patients_patientcase_9f065c57 on patients_patientcase t17 (cost=0.29..0.37 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=13,301)

  • Index Cond: (patients_patient.person_ptr_id = patient_id)
58. 0.000 0.000 ↓ 0.0 0 13,301

Index Scan using analyses_sampleset_b1d48620 on analyses_sampleset t18 (cost=0.29..0.36 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=13,301)

  • Index Cond: (t17.id = patient_case_id)
59. 931.070 931.070 ↓ 43.2 389 13,301

Index Scan using analyses_sampleset_5b8994c1 on analyses_sampleset (cost=0.29..0.59 rows=9 width=8) (actual time=0.002..0.070 rows=389 loops=13,301)

  • Index Cond: (patients_oncologist.person_ptr_id = oncologist_id)
60. 1.035 1.922 ↑ 1.0 4,888 1

Hash (cost=80.88..80.88 rows=4,888 width=24) (actual time=1.922..1.922 rows=4,888 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 294kB
61. 0.887 0.887 ↑ 1.0 4,888 1

Seq Scan on patients_samplesethandling (cost=0.00..80.88 rows=4,888 width=24) (actual time=0.005..0.887 rows=4,888 loops=1)

62. 1.303 2.449 ↑ 1.0 4,888 1

Hash (cost=80.88..80.88 rows=4,888 width=24) (actual time=2.449..2.449 rows=4,888 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 294kB
63. 1.146 1.146 ↑ 1.0 4,888 1

Seq Scan on patients_samplesethandling t19 (cost=0.00..80.88 rows=4,888 width=24) (actual time=0.014..1.146 rows=4,888 loops=1)

64. 43.512 43.512 ↓ 4.0 4 10,878

Index Scan using patients_personhandling_a8452ca7 on patients_personhandling t21 (cost=0.28..0.32 rows=1 width=8) (actual time=0.002..0.004 rows=4 loops=10,878)

  • Index Cond: (patients_person.partner_ptr_id = person_id)
65. 0.011 0.085 ↑ 1.0 115 1

Hash (cost=8.72..8.72 rows=115 width=8) (actual time=0.085..0.085 rows=115 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
66. 0.038 0.074 ↑ 1.0 115 1

Hash Right Join (cost=3.59..8.72 rows=115 width=8) (actual time=0.047..0.074 rows=115 loops=1)

  • Hash Cond: (t23.partner_ptr_id = t22.company_ptr_id)
67. 0.014 0.014 ↑ 1.0 217 1

Seq Scan on patients_company t23 (cost=0.00..3.17 rows=217 width=4) (actual time=0.004..0.014 rows=217 loops=1)

68. 0.011 0.022 ↑ 1.0 115 1

Hash (cost=2.15..2.15 rows=115 width=4) (actual time=0.022..0.022 rows=115 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
69. 0.011 0.011 ↑ 1.0 115 1

Seq Scan on patients_distributioncompany t22 (cost=0.00..2.15 rows=115 width=4) (actual time=0.002..0.011 rows=115 loops=1)

70. 144.150 144.150 ↑ 1.0 1 48,050

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t24 (cost=0.29..4.21 rows=1 width=17) (actual time=0.002..0.003 rows=1 loops=48,050)

  • Index Cond: (t23.partner_ptr_id = id)
71. 0.000 0.000 ↓ 0.0 0 48,050

Index Scan using patients_patientcase_9f065c57 on patients_patientcase t25 (cost=0.29..0.37 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=48,050)

  • Index Cond: (patients_patient.person_ptr_id = patient_id)
72. 528.550 528.550 ↓ 1.9 13 48,050

Index Scan using patients_patientcase_5b8994c1 on patients_patientcase t26 (cost=0.29..0.58 rows=7 width=14) (actual time=0.003..0.011 rows=13 loops=48,050)

  • Index Cond: (patients_oncologist.person_ptr_id = oncologist_id)
73. 1,921.566 1,921.566 ↓ 5.0 5 640,522

Index Scan using patients_personhandling_a8452ca7 on patients_personhandling t27 (cost=0.28..0.32 rows=1 width=24) (actual time=0.001..0.003 rows=5 loops=640,522)

  • Index Cond: (patients_person.partner_ptr_id = person_id)
74. 0.000 0.000 ↓ 0.0 0 3,126,050

Index Scan using patients_patientcase_9f065c57 on patients_patientcase t32 (cost=0.29..0.37 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=3,126,050)

  • Index Cond: (patients_patient.person_ptr_id = patient_id)
75. 0.000 0.000 ↓ 0.0 0 3,126,050

Index Scan using analyses_sampleset_b1d48620 on analyses_sampleset t33 (cost=0.29..0.36 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=3,126,050)

  • Index Cond: (t32.id = patient_case_id)
76. 0.000 0.000 ↓ 0.0 0 3,126,050

Index Scan using patients_samplesethandling_2c3d5d28 on patients_samplesethandling t34 (cost=0.28..0.31 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=3,126,050)

  • Index Cond: (t33.id = sample_set_id)
77. 15,630.250 15,630.250 ↓ 1.4 13 3,126,050

Index Scan using analyses_sampleset_5b8994c1 on analyses_sampleset t29 (cost=0.29..0.59 rows=9 width=8) (actual time=0.001..0.005 rows=13 loops=3,126,050)

  • Index Cond: (patients_oncologist.person_ptr_id = oncologist_id)
78. 39,400.910 39,400.910 ↑ 1.0 1 39,400,910

Index Scan using patients_samplesethandling_2c3d5d28 on patients_samplesethandling t30 (cost=0.28..0.31 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=39,400,910)

  • Index Cond: (t29.id = sample_set_id)
Planning time : 10.741 ms
Execution time : 132,160.000 ms