explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BprR

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 27.221 ↑ 1.0 1 1

Aggregate (cost=3,953.46..3,953.47 rows=1 width=0) (actual time=27.221..27.221 rows=1 loops=1)

2. 0.001 27.218 ↓ 0.0 0 1

HashAggregate (cost=3,953.44..3,953.45 rows=1 width=106) (actual time=27.218..27.218 rows=0 loops=1)

  • Group Key: t35.id, t35.deleted, t35.name, t35.main_account_id, t35.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. 0.000 27.217 ↓ 0.0 0 1

Nested Loop (cost=3,907.15..3,953.39 rows=1 width=106) (actual time=27.217..27.217 rows=0 loops=1)

4. 0.048 27.217 ↓ 0.0 0 1

Hash Right Join (cost=3,906.86..3,953.04 rows=1 width=89) (actual time=27.217..27.217 rows=0 loops=1)

  • Hash Cond: (t29.sample_set_id = t28.id)
  • Filter: (((t26.distribution_company_id = 27,731) AND (t26.ended_at IS NULL) AND (t26.started_at IS NOT NULL)) OR (patients_person.user_id = 13,463) OR ((t29.distribution_company_id = 27,731) AND (t29.ended_at IS NULL) AND (t29.started_at IS NOT NULL)) OR ((t33.distribution_company_id = 27,731) AND (t33.ended_at IS NULL) AND (t33.started_at IS NOT NULL)))
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on patients_samplesethandling t29 (cost=0.00..24.50 rows=1,450 width=24) (never executed)

6. 0.000 27.169 ↓ 0.0 0 1

Hash (cost=3,814.48..3,814.48 rows=7,391 width=133) (actual time=27.169..27.169 rows=0 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 64kB
7. 0.013 27.169 ↓ 0.0 0 1

Hash Right Join (cost=3,416.15..3,814.48 rows=7,391 width=133) (actual time=27.169..27.169 rows=0 loops=1)

  • Hash Cond: (t28.oncologist_id = patients_oncologist.person_ptr_id)
8. 0.000 0.000 ↓ 0.0 0

Seq Scan on analyses_sampleset t28 (cost=0.00..274.95 rows=13,195 width=8) (never executed)

9. 0.000 27.156 ↓ 0.0 0 1

Hash (cost=3,408.13..3,408.13 rows=642 width=133) (actual time=27.156..27.156 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
10. 0.009 27.156 ↓ 0.0 0 1

Hash Right Join (cost=3,377.48..3,408.13 rows=642 width=133) (actual time=27.156..27.156 rows=0 loops=1)

  • Hash Cond: (t33.sample_set_id = t32.id)
11. 0.000 0.000 ↓ 0.0 0

Seq Scan on patients_samplesethandling t33 (cost=0.00..24.50 rows=1,450 width=24) (never executed)

12. 0.000 27.147 ↓ 0.0 0 1

Hash (cost=3,369.46..3,369.46 rows=642 width=117) (actual time=27.147..27.147 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
13. 0.002 27.147 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,233.19..3,369.46 rows=642 width=117) (actual time=27.147..27.147 rows=0 loops=1)

14. 0.000 27.145 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,232.91..3,210.26 rows=364 width=117) (actual time=27.145..27.145 rows=0 loops=1)

15. 0.000 27.145 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,232.62..3,073.05 rows=364 width=117) (actual time=27.145..27.145 rows=0 loops=1)

  • Filter: ((upper((patients_person.firstname)::text) ~~ '%@%'::text) OR (upper((patients_person.lastname)::text) ~~ '%@%'::text) OR (upper((patients_person.email)::text) ~~ '%@%'::text) OR (upper((t23.name)::text) ~~ '%@%'::text) OR (upper((t24.readable_id)::text) ~~ '%@%'::text) OR (upper((t25.readable_id)::text) ~~ '%@%'::text))
16. 0.000 27.145 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,232.34..2,989.88 rows=39 width=130) (actual time=27.145..27.145 rows=0 loops=1)

17. 0.000 27.145 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,232.19..2,976.21 rows=39 width=110) (actual time=27.145..27.145 rows=0 loops=1)

18. 0.001 27.145 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,231.91..2,961.51 rows=39 width=101) (actual time=27.145..27.145 rows=0 loops=1)

19. 0.001 27.144 ↓ 0.0 0 1

Hash Left Join (cost=2,231.62..2,829.20 rows=39 width=101) (actual time=27.144..27.144 rows=0 loops=1)

  • Hash Cond: (t21.company_ptr_id = t22.partner_ptr_id)
20. 0.000 27.143 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,228.37..2,825.79 rows=39 width=101) (actual time=27.143..27.143 rows=0 loops=1)

21. 0.000 27.143 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,228.22..2,817.49 rows=39 width=101) (actual time=27.143..27.143 rows=0 loops=1)

22. 0.001 27.143 ↓ 0.0 0 1

Nested Loop Left Join (cost=2,228.06..2,803.82 rows=39 width=97) (actual time=27.143..27.143 rows=0 loops=1)

  • Filter: ((upper((patients_person.firstname)::text) ~~ '%@%'::text) OR (upper((patients_person.lastname)::text) ~~ '%@%'::text) OR (upper((patients_person.email)::text) ~~ '%@%'::text) OR (upper((patients_partner.name)::text) ~~ '%@%'::text) OR (upper((patients_patientcase.readable_id)::text) ~~ '%@%'::text) OR (upper((t9.readable_id)::text) ~~ '%@%'::text))
23. 2.587 27.142 ↓ 0.0 0 1

Hash Right Join (cost=2,227.78..2,795.29 rows=4 width=110) (actual time=27.142..27.142 rows=0 loops=1)

  • Hash Cond: (analyses_sampleset.oncologist_id = patients_oncologist.person_ptr_id)
  • Filter: (((t10.distribution_company_id = 27,731) AND (t10.ended_at IS NULL) AND (t10.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 ((t18.distribution_company_id = 27,731) AND (t18.ended_at IS NULL) AND (t18.started_at IS NOT NULL)))
  • Rows Removed by Filter: 7,345
24. 1.059 4.368 ↑ 1.0 13,195 1

Hash Right Join (cost=439.89..484.32 rows=13,195 width=24) (actual time=3.422..4.368 rows=13,195 loops=1)

  • Hash Cond: (patients_samplesethandling.sample_set_id = analyses_sampleset.id)
25. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on patients_samplesethandling (cost=0.00..24.50 rows=1,450 width=24) (actual time=0.000..0.000 rows=0 loops=1)

26. 1.526 3.309 ↑ 1.0 13,195 1

Hash (cost=274.95..274.95 rows=13,195 width=8) (actual time=3.309..3.309 rows=13,195 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 625kB
27. 1.783 1.783 ↑ 1.0 13,195 1

Seq Scan on analyses_sampleset (cost=0.00..274.95 rows=13,195 width=8) (actual time=0.015..1.783 rows=13,195 loops=1)

28. 0.513 20.187 ↑ 1.7 1,129 1

Hash (cost=1,763.97..1,763.97 rows=1,914 width=150) (actual time=20.187..20.187 rows=1,129 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 140kB
29. 0.264 19.674 ↑ 1.7 1,129 1

Hash Right Join (cost=1,731.93..1,763.97 rows=1,914 width=150) (actual time=19.426..19.674 rows=1,129 loops=1)

  • Hash Cond: (t18.sample_set_id = t17.id)
30. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on patients_samplesethandling t18 (cost=0.00..24.50 rows=1,450 width=24) (actual time=0.001..0.001 rows=0 loops=1)

31. 0.480 19.409 ↑ 1.7 1,129 1

Hash (cost=1,708.01..1,708.01 rows=1,914 width=134) (actual time=19.409..19.409 rows=1,129 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 140kB
32. 1.690 18.929 ↑ 1.7 1,129 1

Hash Right Join (cost=1,364.44..1,708.01 rows=1,914 width=134) (actual time=18.578..18.929 rows=1,129 loops=1)

  • Hash Cond: (t17.patient_case_id = t16.id)
33. 1.010 1.010 ↑ 1.0 13,195 1

Seq Scan on analyses_sampleset t17 (cost=0.00..274.95 rows=13,195 width=8) (actual time=0.004..1.010 rows=13,195 loops=1)

34. 0.344 16.229 ↓ 1.0 1,129 1

Hash (cost=1,350.87..1,350.87 rows=1,085 width=134) (actual time=16.229..16.229 rows=1,129 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 140kB
35. 1.238 15.885 ↓ 1.0 1,129 1

Hash Right Join (cost=1,134.28..1,350.87 rows=1,085 width=134) (actual time=15.599..15.885 rows=1,129 loops=1)

  • Hash Cond: (t16.patient_id = patients_patient.person_ptr_id)
36. 0.581 0.581 ↑ 1.0 7,477 1

Seq Scan on patients_patientcase t16 (cost=0.00..177.77 rows=7,477 width=8) (actual time=0.002..0.581 rows=7,477 loops=1)

37. 0.366 14.066 ↓ 1.0 1,129 1

Hash (cost=1,120.72..1,120.72 rows=1,085 width=130) (actual time=14.066..14.066 rows=1,129 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 140kB
38. 0.272 13.700 ↓ 1.0 1,129 1

Hash Right Join (cost=1,088.97..1,120.72 rows=1,085 width=130) (actual time=13.443..13.700 rows=1,129 loops=1)

  • Hash Cond: (t10.person_id = patients_person.partner_ptr_id)
39. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on patients_personhandling t10 (cost=0.00..24.50 rows=1,450 width=24) (actual time=0.000..0.000 rows=0 loops=1)

40. 0.458 13.428 ↓ 1.0 1,129 1

Hash (cost=1,075.41..1,075.41 rows=1,085 width=110) (actual time=13.428..13.428 rows=1,129 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 140kB
41. 0.325 12.970 ↓ 1.0 1,129 1

Hash Left Join (cost=834.44..1,075.41 rows=1,085 width=110) (actual time=12.191..12.970 rows=1,129 loops=1)

  • Hash Cond: (patients_person.partner_ptr_id = patients_oncologist.person_ptr_id)
42. 0.155 12.494 ↓ 1.0 1,129 1

Hash Left Join (cost=814.24..1,050.24 rows=1,085 width=106) (actual time=12.026..12.494 rows=1,129 loops=1)

  • Hash Cond: (patients_distributioncompany.company_ptr_id = patients_company.partner_ptr_id)
43. 0.155 10.626 ↓ 1.0 1,129 1

Hash Left Join (cost=637.59..869.09 rows=1,085 width=106) (actual time=10.303..10.626 rows=1,129 loops=1)

  • Hash Cond: (patients_personhandling.distribution_company_id = patients_distributioncompany.company_ptr_id)
44. 1.253 10.471 ↓ 1.0 1,129 1

Hash Right Join (cost=570.21..786.80 rows=1,085 width=106) (actual time=10.282..10.471 rows=1,129 loops=1)

  • Hash Cond: (patients_patientcase.patient_id = patients_patient.person_ptr_id)
45. 0.649 0.649 ↑ 1.0 7,477 1

Seq Scan on patients_patientcase (cost=0.00..177.77 rows=7,477 width=13) (actual time=0.002..0.649 rows=7,477 loops=1)

46. 0.407 8.569 ↓ 1.0 1,129 1

Hash (cost=556.65..556.65 rows=1,085 width=97) (actual time=8.569..8.569 rows=1,129 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 137kB
47. 0.273 8.162 ↓ 1.0 1,129 1

Hash Right Join (cost=524.90..556.65 rows=1,085 width=97) (actual time=7.902..8.162 rows=1,129 loops=1)

  • Hash Cond: (patients_personhandling.person_id = patients_person.partner_ptr_id)
48. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on patients_personhandling (cost=0.00..24.50 rows=1,450 width=8) (actual time=0.000..0.000 rows=0 loops=1)

49. 0.507 7.889 ↓ 1.0 1,129 1

Hash (cost=511.34..511.34 rows=1,085 width=93) (actual time=7.889..7.889 rows=1,129 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 137kB
50. 2.084 7.382 ↓ 1.0 1,129 1

Hash Anti Join (cost=257.02..511.34 rows=1,085 width=93) (actual time=2.508..7.382 rows=1,129 loops=1)

  • Hash Cond: (patients_person.partner_ptr_id = patients_patient.person_ptr_id)
51. 2.916 3.006 ↓ 1.1 8,658 1

Seq Scan on patients_person (cost=53.62..268.22 rows=8,022 width=89) (actual time=0.172..3.006 rows=8,658 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: 49
52.          

SubPlan (for Seq Scan)

53. 0.012 0.058 ↓ 16.3 49 1

Nested Loop (cost=0.57..26.80 rows=3 width=4) (actual time=0.018..0.058 rows=49 loops=1)

54. 0.011 0.011 ↑ 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.011 rows=1 loops=1)

  • Index Cond: ((name)::text = 'SUPER_ADMINS'::text)
55. 0.035 0.035 ↓ 9.8 49 1

Index Scan using authentication_user_groups_0e939a4f on authentication_user_groups u2 (cost=0.29..18.45 rows=5 width=8) (actual time=0.006..0.035 rows=49 loops=1)

  • Index Cond: (group_id = u3.id)
56. 0.014 0.032 ↓ 16.3 49 1

Nested Loop (cost=0.57..26.80 rows=3 width=4) (actual time=0.008..0.032 rows=49 loops=1)

57. 0.005 0.005 ↑ 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.004..0.005 rows=1 loops=1)

  • Index Cond: ((name)::text = 'SUPER_ADMINS'::text)
58. 0.013 0.013 ↓ 9.8 49 1

Index Scan using authentication_user_groups_0e939a4f on authentication_user_groups u2_1 (cost=0.29..18.45 rows=5 width=8) (actual time=0.003..0.013 rows=49 loops=1)

  • Index Cond: (group_id = u3_1.id)
59. 1.369 2.292 ↑ 1.0 7,529 1

Hash (cost=109.29..109.29 rows=7,529 width=4) (actual time=2.292..2.292 rows=7,529 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 329kB
60. 0.923 0.923 ↑ 1.0 7,529 1

Seq Scan on patients_patient (cost=0.00..109.29 rows=7,529 width=4) (actual time=0.007..0.923 rows=7,529 loops=1)

61. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=35.50..35.50 rows=2,550 width=4) (actual time=0.000..0.000 rows=0 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 32kB
62. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on patients_distributioncompany (cost=0.00..35.50 rows=2,550 width=4) (actual time=0.000..0.000 rows=0 loops=1)

63. 0.029 1.713 ↑ 1.0 100 1

Hash (cost=175.40..175.40 rows=100 width=8) (actual time=1.713..1.713 rows=100 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
64. 0.956 1.684 ↑ 1.0 100 1

Hash Right Join (cost=3.25..175.40 rows=100 width=8) (actual time=0.045..1.684 rows=100 loops=1)

  • Hash Cond: (patients_partner.id = patients_company.partner_ptr_id)
65. 0.704 0.704 ↑ 1.0 8,811 1

Seq Scan on patients_partner (cost=0.00..138.11 rows=8,811 width=8) (actual time=0.005..0.704 rows=8,811 loops=1)

66. 0.012 0.024 ↑ 1.0 100 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.024..0.024 rows=100 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
67. 0.012 0.012 ↑ 1.0 100 1

Seq Scan on patients_company (cost=0.00..2.00 rows=100 width=4) (actual time=0.004..0.012 rows=100 loops=1)

68. 0.082 0.151 ↑ 1.0 720 1

Hash (cost=11.20..11.20 rows=720 width=4) (actual time=0.151..0.151 rows=720 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 34kB
69. 0.069 0.069 ↑ 1.0 720 1

Seq Scan on patients_oncologist (cost=0.00..11.20 rows=720 width=4) (actual time=0.004..0.069 rows=720 loops=1)

70. 0.000 0.000 ↓ 0.0 0

Index Scan using patients_patientcase_5b8994c1 on patients_patientcase t9 (cost=0.28..1.33 rows=20 width=13) (never executed)

  • Index Cond: (patients_oncologist.person_ptr_id = oncologist_id)
71. 0.000 0.000 ↓ 0.0 0

Index Scan using patients_personhandling_a8452ca7 on patients_personhandling t20 (cost=0.15..0.28 rows=7 width=8) (never executed)

  • Index Cond: (patients_person.partner_ptr_id = person_id)
72. 0.000 0.000 ↓ 0.0 0

Index Only Scan using patients_distributioncompany_pkey on patients_distributioncompany t21 (cost=0.15..0.20 rows=1 width=4) (never executed)

  • Index Cond: (company_ptr_id = t20.distribution_company_id)
  • Heap Fetches: 0
73. 0.000 0.000 ↓ 0.0 0

Hash (cost=2.00..2.00 rows=100 width=4) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Seq Scan on patients_company t22 (cost=0.00..2.00 rows=100 width=4) (never executed)

75. 0.000 0.000 ↓ 0.0 0

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t23 (cost=0.29..3.38 rows=1 width=8) (never executed)

  • Index Cond: (t22.partner_ptr_id = id)
76. 0.000 0.000 ↓ 0.0 0

Index Scan using patients_patientcase_9f065c57 on patients_patientcase t24 (cost=0.28..0.37 rows=1 width=13) (never executed)

  • Index Cond: (patients_patient.person_ptr_id = patient_id)
77. 0.000 0.000 ↓ 0.0 0

Index Scan using patients_personhandling_a8452ca7 on patients_personhandling t26 (cost=0.15..0.28 rows=7 width=24) (never executed)

  • Index Cond: (patients_person.partner_ptr_id = person_id)
78. 0.000 0.000 ↓ 0.0 0

Index Scan using patients_patientcase_5b8994c1 on patients_patientcase t25 (cost=0.28..1.33 rows=20 width=13) (never executed)

  • Index Cond: (patients_oncologist.person_ptr_id = oncologist_id)
79. 0.000 0.000 ↓ 0.0 0

Index Scan using patients_patientcase_9f065c57 on patients_patientcase t31 (cost=0.28..0.37 rows=1 width=8) (never executed)

  • Index Cond: (patients_patient.person_ptr_id = patient_id)
80. 0.000 0.000 ↓ 0.0 0

Index Scan using analyses_sampleset_b1d48620 on analyses_sampleset t32 (cost=0.29..0.42 rows=2 width=8) (never executed)

  • Index Cond: (t31.id = patient_case_id)
81. 0.000 0.000 ↓ 0.0 0

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t35 (cost=0.29..0.34 rows=1 width=17) (never executed)

  • Index Cond: (id = patients_person.partner_ptr_id)
Planning time : 22.013 ms
Execution time : 28.123 ms