explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lgrx

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 6,018.343 ↓ 30.0 30 1

Limit (cost=2,704.36..2,704.62 rows=1 width=1,022) (actual time=5,952.462..6,018.343 rows=30 loops=1)

2. 63.164 6,018.338 ↓ 30.0 30 1

Unique (cost=2,704.36..2,704.62 rows=1 width=1,022) (actual time=5,952.461..6,018.338 rows=30 loops=1)

3. 1,228.574 5,955.174 ↓ 45,183.0 45,183 1

Sort (cost=2,704.36..2,704.37 rows=1 width=1,022) (actual time=5,952.459..5,955.174 rows=45,183 loops=1)

  • Sort Key: t10.id DESC, t10.deleted, t10.name, t10.main_account_id, t10.data_account_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, t22.id, t22.created_at, t22.updated_at, t22.updated_by_id, t22.created_by_id, t22.deleted, t22.name, t22.is_template, t22.account_contact_information_id, t22.color_palette_id, t22.logo, t22.locale_id, t22.external_id, t22.type_id, t22.status_id, t22.site_id, t22.cancer_list_id, t22.concise_mode, t22.compermed_mode, t22.comment, t22.classification, t22.sample_count_limit, t22.file_count_limit, t22.filesize_per_analysis_limit, t24.id, t24.deleted, t24.name, t24.main_account_id, t24.data_account_id, t23.partner_ptr_id, t23.created_at, t23.updated_at, t23.updated_by_id, t23.created_by_id, t23.firstname, t23.lastname, t23.birthdate, t23.deathdate, t23.email, t23.phone_number, t23.sex_id, t23.country_id, t23.user_id, t23.title_id, patients_sex.id, patients_sex.acronym, patients_sex.name, countries_country.iso, countries_country.name, authentication_user.id, authentication_user.password, authentication_user.last_login, authentication_user.is_superuser, authentication_user.created_at, authentication_user.updated_at, authentication_user.updated_by_id, authentication_user.created_by_id, authentication_user.email, authentication_user.interface_language, authentication_user.is_admin, authentication_user.skip_upload_help, authentication_user.recovery_token, authentication_user.geolocated_country_id, authentication_user.latest_version_ack_id, authentication_user.is_internal, authentication_user.is_public_inter
  • Sort Method: quicksort Memory: 46,911kB
4. 30.329 4,726.600 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,180.78..2,704.35 rows=1 width=1,022) (actual time=40.677..4,726.600 rows=45,194 loops=1)

5. 316.447 4,696.271 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,180.50..2,702.93 rows=1 width=994) (actual time=40.672..4,696.271 rows=45,194 loops=1)

  • Join Filter: (t30.company_ptr_id = t31.partner_ptr_id)
  • Rows Removed by Join Filter: 6,101,190
6. 248.865 4,063.466 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,180.50..2,698.90 rows=1 width=990) (actual time=40.657..4,063.466 rows=45,194 loops=1)

  • Join Filter: (patients_distributor.distribution_company_id = t30.company_ptr_id)
  • Rows Removed by Join Filter: 4,926,146
7. 414.009 3,543.437 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,180.50..2,695.44 rows=1 width=986) (actual time=40.642..3,543.437 rows=45,194 loops=1)

  • Join Filter: (patients_person.partner_ptr_id = patients_distributor.person_ptr_id)
  • Rows Removed by Join Filter: 7,863,756
8. 865.747 2,677.488 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,180.50..2,690.53 rows=1 width=978) (actual time=40.620..2,677.488 rows=45,194 loops=1)

  • Join Filter: (patients_person.partner_ptr_id = patients_biologist.person_ptr_id)
  • Rows Removed by Join Filter: 17,761,242
9. 28.918 862.667 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,180.50..2,679.69 rows=1 width=974) (actual time=40.579..862.667 rows=45,194 loops=1)

  • Join Filter: (patients_person.title_id = patients_title.id)
  • Rows Removed by Join Filter: 271,114
10. 97.109 788.555 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,180.50..2,678.55 rows=1 width=908) (actual time=40.574..788.555 rows=45,194 loops=1)

  • Join Filter: ((patients_person.country_id)::text = (countries_country.iso)::text)
  • Rows Removed by Join Filter: 917,973
11. 27.474 601.058 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,180.50..2,669.93 rows=1 width=895) (actual time=40.546..601.058 rows=45,194 loops=1)

12. 31.190 528.390 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,180.21..2,669.61 rows=1 width=867) (actual time=40.538..528.390 rows=45,194 loops=1)

13. 36.759 452.006 ↓ 45,194.0 45,194 1

Nested Loop (cost=2,179.92..2,669.26 rows=1 width=765) (actual time=40.529..452.006 rows=45,194 loops=1)

  • Join Filter: (t20.partner_id = patients_person.partner_ptr_id)
14. 61.276 370.053 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,179.63..2,668.91 rows=1 width=769) (actual time=40.521..370.053 rows=45,194 loops=1)

  • Join Filter: ((patients_person.sex_id)::text = (patients_sex.id)::text)
  • Rows Removed by Join Filter: 45,209
15. 112.748 308.777 ↓ 45,194.0 45,194 1

Nested Loop Left Join (cost=2,179.63..2,667.87 rows=1 width=435) (actual time=40.515..308.777 rows=45,194 loops=1)

  • Filter: ((upper((patients_person.firstname)::text) ~~ '%20200%'::text) OR (upper((patients_person.lastname)::text) ~~ '%20200%'::text) OR (upper((patients_person.email)::text) ~~ '%20200%'::text) OR (upper((t17.name)::text) ~~ '%20200%'::text) OR (upper((t18.readable_id)::text) ~~ '%20200%'::text) OR (upper((t19.readable_id)::text) ~~ '%20200%'::text))
  • Rows Removed by Filter: 3,800
16. 22.618 196.029 ↓ 2,227.0 48,994 1

Nested Loop Left Join (cost=2,179.35..2,659.92 rows=22 width=462) (actual time=39.839..196.029 rows=48,994 loops=1)

17. 15.007 124.417 ↓ 2,227.0 48,994 1

Nested Loop Left Join (cost=2,179.20..2,656.34 rows=22 width=330) (actual time=39.833..124.417 rows=48,994 loops=1)

18. 1.945 91.656 ↓ 269.0 5,918 1

Nested Loop Left Join (cost=2,178.92..2,646.32 rows=22 width=322) (actual time=39.809..91.656 rows=5,918 loops=1)

19. 0.663 58.141 ↓ 18.6 410 1

Nested Loop Left Join (cost=2,178.06..2,599.80 rows=22 width=307) (actual time=39.656..58.141 rows=410 loops=1)

20. 0.569 56.658 ↓ 18.6 410 1

Nested Loop (cost=2,177.77..2,590.57 rows=22 width=142) (actual time=39.643..56.658 rows=410 loops=1)

  • Join Filter: (patients_person.partner_ptr_id = t10.id)
21. 0.605 55.679 ↓ 18.6 410 1

Nested Loop (cost=2,177.49..2,583.32 rows=22 width=114) (actual time=39.628..55.679 rows=410 loops=1)

22. 17.165 54.639 ↓ 18.1 435 1

Hash Right Join (cost=2,177.20..2,574.49 rows=24 width=110) (actual time=39.613..54.639 rows=435 loops=1)

  • Hash Cond: (patients_patientcase.patient_id = patients_patient.person_ptr_id)
  • Filter: ((upper((patients_person.firstname)::text) ~~ '%20200%'::text) OR (upper((patients_person.lastname)::text) ~~ '%20200%'::text) OR (upper((patients_person.email)::text) ~~ '%20200%'::text) OR (upper((patients_partner.name)::text) ~~ '%20200%'::text) OR (upper((patients_patientcase.readable_id)::text) ~~ '%20200%'::text) OR (upper((t9.readable_id)::text) ~~ '%20200%'::text))
  • Rows Removed by Filter: 11,593
23. 0.712 0.712 ↑ 1.0 10,823 1

Seq Scan on patients_patientcase (cost=0.00..256.23 rows=10,823 width=16) (actual time=0.007..0.712 rows=10,823 loops=1)

24. 3.233 36.762 ↓ 4.8 12,028 1

Hash (cost=2,145.93..2,145.93 rows=2,501 width=137) (actual time=36.739..36.762 rows=12,028 loops=1)

  • Buckets: 16,384 (originally 4096) Batches: 1 (originally 1) Memory Usage: 1,703kB
25. 2.859 33.529 ↓ 4.8 12,028 1

Hash Anti Join (cost=1,627.23..2,145.93 rows=2,501 width=137) (actual time=21.724..33.529 rows=12,028 loops=1)

  • Hash Cond: (patients_person.partner_ptr_id = patients_patient.person_ptr_id)
26. 5.091 29.124 ↓ 3.5 22,723 1

Hash Right Join (cost=1,341.68..1,813.93 rows=6,471 width=133) (actual time=20.139..29.124 rows=22,723 loops=1)

  • Hash Cond: (patients_oncologist.person_ptr_id = patients_person.partner_ptr_id)
27. 1.348 3.924 ↓ 1.8 10,797 1

Merge Left Join (cost=0.57..427.44 rows=6,054 width=16) (actual time=0.028..3.924 rows=10,797 loops=1)

  • Merge Cond: (patients_oncologist.person_ptr_id = t9.oncologist_id)
28. 1.106 1.106 ↑ 1.0 6,054 1

Index Only Scan using patients_oncologist_pkey on patients_oncologist (cost=0.28..139.09 rows=6,054 width=4) (actual time=0.018..1.106 rows=6,054 loops=1)

  • Heap Fetches: 6,054
29. 1.470 1.470 ↑ 1.8 6,050 1

Index Scan using patients_patientcase_5b8994c1 on patients_patientcase t9 (cost=0.29..353.33 rows=10,823 width=16) (actual time=0.006..1.470 rows=6,050 loops=1)

30. 4.664 20.109 ↓ 2.7 17,413 1

Hash (cost=1,260.23..1,260.23 rows=6,471 width=117) (actual time=20.089..20.109 rows=17,413 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,233kB
31. 3.943 15.445 ↓ 2.7 17,413 1

Hash Right Join (cost=1,063.16..1,260.23 rows=6,471 width=117) (actual time=10.104..15.445 rows=17,413 loops=1)

  • Hash Cond: (patients_personhandling.person_id = patients_person.partner_ptr_id)
32. 1.041 1.681 ↑ 1.0 5,993 1

Hash Left Join (cost=180.49..361.82 rows=5,993 width=19) (actual time=0.277..1.681 rows=5,993 loops=1)

  • Hash Cond: (patients_personhandling.distribution_company_id = patients_distributioncompany.company_ptr_id)
33. 0.375 0.375 ↑ 1.0 5,993 1

Seq Scan on patients_personhandling (cost=0.00..98.93 rows=5,993 width=8) (actual time=0.007..0.375 rows=5,993 loops=1)

34. 0.017 0.265 ↑ 1.0 109 1

Hash (cost=179.13..179.13 rows=109 width=19) (actual time=0.263..0.265 rows=109 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
35. 0.020 0.248 ↑ 1.0 109 1

Nested Loop Left Join (cost=0.43..179.13 rows=109 width=19) (actual time=0.029..0.248 rows=109 loops=1)

36. 0.010 0.010 ↑ 1.0 109 1

Seq Scan on patients_distributioncompany (cost=0.00..2.09 rows=109 width=4) (actual time=0.004..0.010 rows=109 loops=1)

37. 0.000 0.218 ↑ 1.0 1 109

Nested Loop Left Join (cost=0.43..1.61 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=109)

38. 0.109 0.109 ↑ 1.0 1 109

Index Only Scan using patients_company_pkey on patients_company (cost=0.14..0.19 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=109)

  • Index Cond: (partner_ptr_id = patients_distributioncompany.company_ptr_id)
  • Heap Fetches: 109
39. 0.109 0.109 ↑ 1.0 1 109

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner (cost=0.29..1.42 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=109)

  • Index Cond: (id = patients_company.partner_ptr_id)
40. 4.308 9.821 ↓ 2.7 17,186 1

Hash (cost=801.78..801.78 rows=6,471 width=102) (actual time=9.806..9.821 rows=17,186 loops=1)

  • Buckets: 32,768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2,111kB
41. 5.272 5.513 ↓ 2.7 17,186 1

Seq Scan on patients_person (cost=317.60..801.78 rows=6,471 width=102) (actual time=0.272..5.513 rows=17,186 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: 93
42.          

SubPlan (for Seq Scan)

43. 0.022 0.182 ↓ 1.6 93 1

Nested Loop (cost=3.05..158.65 rows=59 width=4) (actual time=0.038..0.182 rows=93 loops=1)

44. 0.015 0.015 ↑ 1.0 1 1

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

  • Index Cond: ((name)::text = 'SUPER_ADMINS'::text)
45. 0.131 0.145 ↑ 1.9 93 1

Bitmap Heap Scan on authentication_user_groups u2 (cost=2.77..154.38 rows=178 width=8) (actual time=0.022..0.145 rows=93 loops=1)

  • Recheck Cond: (group_id = u3.id)
  • Heap Blocks: exact=66
46. 0.014 0.014 ↑ 1.9 93 1

Bitmap Index Scan on authentication_user_groups_0e939a4f (cost=0.00..2.73 rows=178 width=0) (actual time=0.014..0.014 rows=93 loops=1)

  • Index Cond: (group_id = u3.id)
47. 0.007 0.059 ↓ 1.6 93 1

Nested Loop (cost=3.05..158.65 rows=59 width=4) (actual time=0.022..0.059 rows=93 loops=1)

48. 0.005 0.005 ↑ 1.0 1 1

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

  • Index Cond: ((name)::text = 'SUPER_ADMINS'::text)
49. 0.035 0.047 ↑ 1.9 93 1

Bitmap Heap Scan on authentication_user_groups u2_1 (cost=2.77..154.38 rows=178 width=8) (actual time=0.017..0.047 rows=93 loops=1)

  • Recheck Cond: (group_id = u3_1.id)
  • Heap Blocks: exact=66
50. 0.012 0.012 ↑ 1.9 93 1

Bitmap Index Scan on authentication_user_groups_0e939a4f (cost=0.00..2.73 rows=178 width=0) (actual time=0.012..0.012 rows=93 loops=1)

  • Index Cond: (group_id = u3_1.id)
51. 0.930 1.546 ↓ 1.0 10,603 1

Hash (cost=153.02..153.02 rows=10,602 width=4) (actual time=1.545..1.546 rows=10,603 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 501kB
52. 0.616 0.616 ↓ 1.0 10,603 1

Seq Scan on patients_patient (cost=0.00..153.02 rows=10,602 width=4) (actual time=0.010..0.616 rows=10,603 loops=1)

53. 0.435 0.435 ↑ 1.0 1 435

Index Only Scan using patients_accountmembership_account_id_a1223ad4_uniq on patients_accountmembership (cost=0.29..0.37 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=435)

  • Index Cond: ((account_id = 170) AND (partner_id = patients_person.partner_ptr_id))
  • Heap Fetches: 0
54. 0.410 0.410 ↑ 1.0 1 410

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t10 (cost=0.29..0.32 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=410)

  • Index Cond: (id = patients_accountmembership.partner_id)
55. 0.820 0.820 ↑ 1.0 1 410

Index Scan using authentication_user_pkey on authentication_user (cost=0.29..0.42 rows=1 width=165) (actual time=0.002..0.002 rows=1 loops=410)

  • Index Cond: (id = patients_person.user_id)
56. 5.622 31.570 ↓ 14.0 14 410

Nested Loop Left Join (cost=0.86..2.10 rows=1 width=19) (actual time=0.006..0.077 rows=14 loops=410)

57. 2.460 2.460 ↓ 14.0 14 410

Index Scan using patients_personhandling_a8452ca7 on patients_personhandling t14 (cost=0.28..0.31 rows=1 width=8) (actual time=0.002..0.006 rows=14 loops=410)

  • Index Cond: (person_id = patients_person.partner_ptr_id)
58. 0.000 23.488 ↑ 1.0 1 5,872

Nested Loop Left Join (cost=0.57..1.78 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=5,872)

59. 5.872 5.872 ↑ 1.0 1 5,872

Index Only Scan using patients_distributioncompany_pkey on patients_distributioncompany t15 (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=5,872)

  • Index Cond: (company_ptr_id = t14.distribution_company_id)
  • Heap Fetches: 5,872
60. 5.872 17.616 ↑ 1.0 1 5,872

Nested Loop Left Join (cost=0.43..1.61 rows=1 width=19) (actual time=0.002..0.003 rows=1 loops=5,872)

61. 5.872 5.872 ↑ 1.0 1 5,872

Index Only Scan using patients_company_pkey on patients_company t16 (cost=0.14..0.19 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=5,872)

  • Index Cond: (partner_ptr_id = t15.company_ptr_id)
  • Heap Fetches: 5,872
62. 5.872 5.872 ↑ 1.0 1 5,872

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t17 (cost=0.29..1.42 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=5,872)

  • Index Cond: (id = t16.partner_ptr_id)
63. 17.754 17.754 ↓ 1.6 8 5,918

Index Scan using patients_patientcase_5b8994c1 on patients_patientcase t19 (cost=0.29..0.41 rows=5 width=16) (actual time=0.001..0.003 rows=8 loops=5,918)

  • Index Cond: (oncologist_id = patients_oncologist.person_ptr_id)
64. 48.994 48.994 ↑ 1.0 1 48,994

Index Scan using accounts_account_pkey on accounts_account t22 (cost=0.14..0.16 rows=1 width=132) (actual time=0.001..0.001 rows=1 loops=48,994)

  • Index Cond: (id = t10.main_account_id)
65. 0.000 0.000 ↓ 0.0 0 48,994

Index Scan using patients_patientcase_9f065c57 on patients_patientcase t18 (cost=0.29..0.32 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=48,994)

  • Index Cond: (patient_id = patients_patient.person_ptr_id)
66. 0.000 0.000 ↑ 1.0 2 45,194

Seq Scan on patients_sex (cost=0.00..1.02 rows=2 width=334) (actual time=0.000..0.000 rows=2 loops=45,194)

67. 45.194 45.194 ↑ 1.0 1 45,194

Index Only Scan using patients_accountmembership_account_id_a1223ad4_uniq on patients_accountmembership t20 (cost=0.29..0.33 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=45,194)

  • Index Cond: ((account_id = 170) AND (partner_id = t10.id))
  • Heap Fetches: 0
68. 45.194 45.194 ↑ 1.0 1 45,194

Index Scan using patients_person_partner_ptr_id_a341a131_uniq on patients_person t23 (cost=0.29..0.35 rows=1 width=102) (actual time=0.001..0.001 rows=1 loops=45,194)

  • Index Cond: (partner_ptr_id = patients_person.created_by_id)
69. 45.194 45.194 ↑ 1.0 1 45,194

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t24 (cost=0.29..0.32 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=45,194)

  • Index Cond: (id = t23.partner_ptr_id)
70. 90.388 90.388 ↑ 11.9 21 45,194

Seq Scan on countries_country (cost=0.00..5.50 rows=250 width=13) (actual time=0.001..0.002 rows=21 loops=45,194)

71. 45.194 45.194 ↑ 1.0 6 45,194

Seq Scan on patients_title (cost=0.00..1.06 rows=6 width=66) (actual time=0.000..0.001 rows=6 loops=45,194)

72. 949.074 949.074 ↑ 1.0 393 45,194

Seq Scan on patients_biologist (cost=0.00..5.93 rows=393 width=4) (actual time=0.003..0.021 rows=393 loops=45,194)

73. 451.940 451.940 ↑ 1.0 174 45,194

Seq Scan on patients_distributor (cost=0.00..2.74 rows=174 width=8) (actual time=0.002..0.010 rows=174 loops=45,194)

74. 271.164 271.164 ↑ 1.0 109 45,194

Seq Scan on patients_distributioncompany t30 (cost=0.00..2.09 rows=109 width=4) (actual time=0.002..0.006 rows=109 loops=45,194)

75. 316.358 316.358 ↑ 1.0 135 45,194

Seq Scan on patients_company t31 (cost=0.00..2.35 rows=135 width=4) (actual time=0.002..0.007 rows=135 loops=45,194)

76. 0.000 0.000 ↓ 0.0 0 45,194

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t32 (cost=0.29..1.42 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=45,194)

  • Index Cond: (id = t31.partner_ptr_id)
Planning time : 306.248 ms
Execution time : 6,020.911 ms