explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2LmV

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 125,545.476 ↑ 1.0 1 1

Aggregate (cost=6,366.75..6,366.76 rows=1 width=0) (actual time=125,545.476..125,545.476 rows=1 loops=1)

2. 30,847.788 125,545.470 ↓ 15.7 47 1

HashAggregate (cost=6,366.69..6,366.72 rows=3 width=127) (actual time=125,545.459..125,545.470 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. 22,369.410 94,697.682 ↓ 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,783.511..94,697.682 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. 5,663.679 32,927.362 ↓ 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,783.503..32,927.362 rows=39,400,910 loops=1)

5. 1,179.064 11,633.433 ↓ 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,783.500..11,633.433 rows=3,126,050 loops=1)

6. 1,231.675 10,454.369 ↓ 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,783.499..10,454.369 rows=3,126,050 loops=1)

7. 1,257.322 9,222.694 ↓ 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,783.497..9,222.694 rows=3,126,050 loops=1)

8. 995.392 7,965.372 ↓ 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,783.496..7,965.372 rows=3,126,050 loops=1)

9. 1,157.800 5,688.936 ↓ 71,169.1 640,522 1

Nested Loop Left Join (cost=4,327.29..6,341.75 rows=9 width=135) (actual time=2,783.493..5,688.936 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. 41.760 4,098.686 ↓ 429.0 48,050 1

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

11. 67.543 4,056.926 ↓ 429.0 48,050 1

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

12. 47.568 3,893.283 ↓ 429.0 48,050 1

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

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

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

14. 1,120.846 3,784.395 ↓ 97.1 10,878 1

Hash Left Join (cost=4,315.99..5,681.13 rows=112 width=135) (actual time=2,783.361..3,784.395 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. 919.575 2,662.006 ↓ 7,911.3 5,197,732 1

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

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

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

17. 7.586 163.959 ↓ 40.2 13,301 1

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

18. 8.236 156.373 ↓ 40.9 13,301 1

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

19. 3.527 148.137 ↓ 40.9 13,301 1

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

20. 45.605 123.052 ↓ 33.2 10,779 1

Hash Right Join (cost=4,030.88..4,803.32 rows=325 width=135) (actual time=66.758..123.052 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.315 12.366 ↓ 1.4 18,848 1

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

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

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

23. 0.968 1.570 ↑ 1.0 7,464 1

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

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

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

25. 4.982 65.081 ↓ 2.0 7,990 1

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

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

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

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

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

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

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

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

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

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

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

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

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

32. 2.957 34.779 ↓ 2.1 8,206 1

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

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

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

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

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

35. 3.641 27.541 ↓ 2.0 8,052 1

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

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

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

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

Seq Scan on patients_person (cost=446.17..1,176.34 rows=13,124 width=101) (actual time=0.378..11.002 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.019 0.182 ↓ 4.4 128 1

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

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

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

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

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

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

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

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

44. 0.003 0.003 ↑ 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.003 rows=1 loops=1)

  • Index Cond: ((name)::text = 'SUPER_ADMINS'::text)
45. 0.056 0.076 ↓ 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.028..0.076 rows=128 loops=1)

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

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

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

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

  • Buckets: 32,768 Batches: 1 Memory Usage: 912kB
48. 1.645 1.645 ↑ 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.645 rows=18,637 loops=1)

49. 0.025 0.314 ↑ 1.0 115 1

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

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

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

51. 0.043 0.089 ↑ 1.0 115 1

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

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

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

53. 0.017 0.028 ↑ 1.0 115 1

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

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

Seq Scan on patients_distributioncompany (cost=0.00..2.15 rows=115 width=4) (actual time=0.004..0.011 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. 0.874 1.565 ↑ 1.0 4,888 1

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

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

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

62. 0.823 1.543 ↑ 1.0 4,888 1

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

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

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

64. 32.634 32.634 ↓ 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.001..0.003 rows=4 loops=10,878)

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

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

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

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

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

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

68. 0.025 0.031 ↑ 1.0 115 1

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

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

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

70. 96.100 96.100 ↑ 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.002 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. 432.450 432.450 ↓ 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.002..0.009 rows=13 loops=48,050)

  • Index Cond: (patients_oncologist.person_ptr_id = oncologist_id)
73. 1,281.044 1,281.044 ↓ 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.002 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 : 9.856 ms
Execution time : 125,546.536 ms