explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PLW5

Settings
# exclusive inclusive rows x rows loops node
1. 2.884 240.162 ↑ 1.0 1 1

Unique (cost=47.26..47.27 rows=1 width=12) (actual time=234.976..240.162 rows=1 loops=1)

2. 10.727 237.278 ↓ 60,000.0 60,000 1

Sort (cost=47.26..47.27 rows=1 width=12) (actual time=234.975..237.278 rows=60,000 loops=1)

  • Sort Key: patients_patientcase.created_at DESC
  • Sort Method: quicksort Memory: 4,349kB
3. 8.169 226.551 ↓ 60,000.0 60,000 1

Nested Loop (cost=14.32..47.25 rows=1 width=12) (actual time=0.410..226.551 rows=60,000 loops=1)

4. 2.508 158.382 ↓ 60,000.0 60,000 1

Nested Loop (cost=14.04..44.74 rows=1 width=16) (actual time=0.403..158.382 rows=60,000 loops=1)

5. 7.997 131.874 ↓ 12,000.0 12,000 1

Nested Loop Left Join (cost=13.75..42.23 rows=1 width=12) (actual time=0.396..131.874 rows=12,000 loops=1)

  • Filter: ((upper((analyses_cancerlocation.name)::text) ~~ '%2020%'::text) OR (upper((t25.external_id)::text) ~~ '%2020%'::text) OR (upper((patients_patientcase.readable_id)::text) ~~ '%2020%'::text) OR (upper((t25.barcode)::text) ~~ '%2020%'::text) OR (upper((t24.barcode)::text) ~~ '%2020%'::text) OR (upper((patients_person.lastname)::text) ~~ '%2020%'::text) OR (upper((patients_patientcase.created_at)::text) ~~ '%2020%'::text) OR (upper((t25.internal_id)::text) ~~ '%2020%'::text) OR (upper((t26.barcode)::text) ~~ '%2020%'::text) OR (upper((patients_person.email)::text) ~~ '%2020%'::text) OR (upper((patients_person.firstname)::text) ~~ '%2020%'::text) OR (upper((t30.name)::text) ~~ '%2020%'::text) OR (upper((analyses_cancer.name)::text) ~~ '%2020%'::text) OR (upper((t6.firstname)::text) ~~ '%2020%'::text) OR (upper((t6.lastname)::text) ~~ '%2020%'::text) OR (upper((t6.email)::text) ~~ '%2020%'::text))
6. 4.769 63.877 ↓ 12,000.0 12,000 1

Nested Loop Left Join (cost=12.90..37.88 rows=1 width=195) (actual time=0.362..63.877 rows=12,000 loops=1)

7. 0.704 47.108 ↓ 12,000.0 12,000 1

Nested Loop Left Join (cost=12.61..35.37 rows=1 width=186) (actual time=0.353..47.108 rows=12,000 loops=1)

8. 2.552 34.404 ↓ 12,000.0 12,000 1

Nested Loop Left Join (cost=12.32..32.86 rows=1 width=158) (actual time=0.344..34.404 rows=12,000 loops=1)

  • Join Filter: (patients_patientcase.id = t24.patient_case_id)
9. 2.276 27.052 ↓ 2,400.0 2,400 1

Nested Loop Left Join (cost=12.03..30.34 rows=1 width=141) (actual time=0.335..27.052 rows=2,400 loops=1)

  • Filter: ((upper((analyses_cancerlocation.name)::text) ~~ '%2020%'::text) OR (upper((analyses_sample.external_id)::text) ~~ '%2020%'::text) OR (upper((patients_patientcase.readable_id)::text) ~~ '%2020%'::text) OR (upper((analyses_sample.barcode)::te
10. 1.132 12.776 ↓ 2,400.0 2,400 1

Nested Loop Left Join (cost=11.17..25.99 rows=1 width=195) (actual time=0.295..12.776 rows=2,400 loops=1)

11. 1.172 9.244 ↓ 2,400.0 2,400 1

Nested Loop Left Join (cost=10.90..23.49 rows=1 width=170) (actual time=0.275..9.244 rows=2,400 loops=1)

12. 0.306 5.672 ↓ 2,400.0 2,400 1

Nested Loop Left Join (cost=10.61..20.99 rows=1 width=161) (actual time=0.245..5.672 rows=2,400 loops=1)

13. 0.542 2.966 ↓ 2,400.0 2,400 1

Nested Loop Left Join (cost=10.32..18.47 rows=1 width=133) (actual time=0.231..2.966 rows=2,400 loops=1)

  • Join Filter: (patients_patientcase.id = t16.patient_case_id)
14. 0.323 1.464 ↓ 480.0 480 1

Nested Loop Left Join (cost=10.03..15.95 rows=1 width=116) (actual time=0.223..1.464 rows=480 loops=1)

  • Filter: (((patients_personhandling.distribution_company_id = 28,279) AND (patients_personhandling.ended_at IS NULL) AND (patients_personhandling.started_at IS NOT NULL)) OR ((t10.distribution_company_id = 28,279) AND (t10.ended_at IS NULL) AND (t10.started_at IS NOT NULL)) OR ((patients_samplesethandling.distribution_company_id = 28,279) AND (patients_samplesethandling.ended_at IS NULL) AND (patients_samplesethandling.started_at IS NOT NULL)))
15. 0.126 0.661 ↓ 480.0 480 1

Nested Loop Left Join (cost=9.75..13.43 rows=1 width=160) (actual time=0.212..0.661 rows=480 loops=1)

  • Join Filter: (patients_patientcase.id = analyses_sampleset.patient_case_id)
16. 0.055 0.343 ↓ 96.0 96 1

Nested Loop Left Join (cost=9.46..10.92 rows=1 width=156) (actual time=0.197..0.343 rows=96 loops=1)

17. 0.037 0.210 ↓ 6.0 6 1

Hash Right Join (cost=9.18..10.60 rows=1 width=140) (actual time=0.188..0.210 rows=6 loops=1)

  • Hash Cond: (analyses_cancerlocation.id = patients_patientcase.cancer_primary_site_id)
18. 0.030 0.030 ↑ 1.0 30 1

Seq Scan on analyses_cancerlocation (cost=0.00..1.30 rows=30 width=14) (actual time=0.027..0.030 rows=30 loops=1)

19. 0.011 0.143 ↓ 6.0 6 1

Hash (cost=9.17..9.17 rows=1 width=134) (actual time=0.138..0.143 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
20. 0.004 0.132 ↓ 6.0 6 1

Nested Loop Left Join (cost=2.29..9.17 rows=1 width=134) (actual time=0.118..0.132 rows=6 loops=1)

21. 0.006 0.108 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.01..8.85 rows=1 width=118) (actual time=0.101..0.108 rows=1 loops=1)

  • Filter: (((NOT patients_partner.deleted) AND (NOT t7.deleted)) OR ((NOT patients_partner.deleted) AND (patients_patientcase.patient_id IS NULL)) OR ((patients_patientcase.oncologist_id IS NULL) AND (NOT t7.deleted)))
22. 0.003 0.073 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.15..5.66 rows=1 width=83) (actual time=0.069..0.073 rows=1 loops=1)

23. 0.006 0.061 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.86..5.34 rows=1 width=82) (actual time=0.058..0.061 rows=1 loops=1)

24. 0.004 0.040 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..5.01 rows=1 width=42) (actual time=0.038..0.040 rows=1 loops=1)

25. 0.021 0.021 ↑ 1.0 1 1

Index Scan using patients_patientcase_pkey on patients_patientcase (cost=0.29..2.51 rows=1 width=38) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: (id = 19,458)
  • Filter: (NOT deleted)
26. 0.015 0.015 ↑ 1.0 1 1

Index Only Scan using patients_patient_pkey on patients_patient (cost=0.29..2.51 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (person_ptr_id = patients_patientcase.patient_id)
  • Heap Fetches: 1
27. 0.015 0.015 ↑ 1.0 1 1

Index Scan using patients_person_partner_ptr_id_a341a131_uniq on patients_person t6 (cost=0.29..0.33 rows=1 width=44) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (partner_ptr_id = patients_patient.person_ptr_id)
28. 0.009 0.009 ↑ 1.0 1 1

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t7 (cost=0.29..0.32 rows=1 width=5) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (id = t6.partner_ptr_id)
29. 0.002 0.029 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.86..3.18 rows=1 width=49) (actual time=0.027..0.029 rows=1 loops=1)

30. 0.003 0.021 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..2.87 rows=1 width=48) (actual time=0.020..0.021 rows=1 loops=1)

31. 0.012 0.012 ↑ 1.0 1 1

Index Only Scan using patients_oncologist_pkey on patients_oncologist (cost=0.28..2.50 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (person_ptr_id = patients_patientcase.oncologist_id)
  • Heap Fetches: 1
32. 0.006 0.006 ↑ 1.0 1 1

Index Scan using patients_person_partner_ptr_id_a341a131_uniq on patients_person (cost=0.29..0.37 rows=1 width=44) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (partner_ptr_id = patients_oncologist.person_ptr_id)
33. 0.006 0.006 ↑ 1.0 1 1

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner (cost=0.29..0.32 rows=1 width=5) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = patients_person.partner_ptr_id)
34. 0.020 0.020 ↓ 6.0 6 1

Index Scan using patients_personhandling_a8452ca7 on patients_personhandling (cost=0.29..0.31 rows=1 width=24) (actual time=0.015..0.020 rows=6 loops=1)

  • Index Cond: (person_id = t6.partner_ptr_id)
35. 0.078 0.078 ↓ 16.0 16 6

Index Scan using patients_personhandling_a8452ca7 on patients_personhandling t10 (cost=0.29..0.31 rows=1 width=24) (actual time=0.003..0.013 rows=16 loops=6)

  • Index Cond: (person_id = patients_person.partner_ptr_id)
36. 0.192 0.192 ↓ 5.0 5 96

Index Scan using analyses_sampleset_b1d48620 on analyses_sampleset (cost=0.29..2.51 rows=1 width=8) (actual time=0.001..0.002 rows=5 loops=96)

  • Index Cond: (patient_case_id = 19,458)
37. 0.480 0.480 ↑ 1.0 1 480

Index Scan using patients_samplesethandling_2c3d5d28 on patients_samplesethandling (cost=0.28..2.50 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=480)

  • Index Cond: (sample_set_id = analyses_sampleset.id)
38. 0.960 0.960 ↓ 5.0 5 480

Index Scan using analyses_sampleset_b1d48620 on analyses_sampleset t16 (cost=0.29..2.51 rows=1 width=21) (actual time=0.001..0.002 rows=5 loops=480)

  • Index Cond: (patient_case_id = 19,458)
39. 2.400 2.400 ↓ 0.0 0 2,400

Index Scan using analyses_sample_2c3d5d28 on analyses_sample (cost=0.29..2.51 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=2,400)

  • Index Cond: (sample_set_id = t16.id)
40. 2.400 2.400 ↑ 1.0 1 2,400

Index Scan using kits_kit_pkey on kits_kit (cost=0.29..2.51 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=2,400)

  • Index Cond: (id = t16.kit_id)
41. 2.400 2.400 ↑ 1.0 1 2,400

Index Scan using analyses_cancer_pkey on analyses_cancer (cost=0.28..2.49 rows=1 width=33) (actual time=0.001..0.001 rows=1 loops=2,400)

  • Index Cond: (id = patients_patientcase.cancer_id)
42. 2.400 12.000 ↑ 1.0 1 2,400

Nested Loop Left Join (cost=0.86..4.25 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=2,400)

43. 0.000 7.200 ↑ 1.0 1 2,400

Nested Loop Left Join (cost=0.57..2.85 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,400)

44. 0.000 4.800 ↑ 1.0 1 2,400

Nested Loop Left Join (cost=0.42..2.66 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,400)

45. 2.400 2.400 ↑ 1.0 1 2,400

Index Scan using patients_samplesethandling_2c3d5d28 on patients_samplesethandling t19 (cost=0.28..2.50 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=2,400)

  • Index Cond: (sample_set_id = t16.id)
46. 2.400 2.400 ↑ 1.0 1 2,400

Index Only Scan using patients_distributioncompany_pkey on patients_distributioncompany t20 (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=2,400)

  • Index Cond: (company_ptr_id = t19.distribution_company_id)
  • Heap Fetches: 2,400
47. 2.400 2.400 ↑ 1.0 1 2,400

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=2,400)

  • Index Cond: (partner_ptr_id = t20.company_ptr_id)
  • Heap Fetches: 2,400
48. 2.400 2.400 ↑ 1.0 1 2,400

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t22 (cost=0.29..1.40 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=2,400)

  • Index Cond: (id = patients_company.partner_ptr_id)
49. 4.800 4.800 ↓ 5.0 5 2,400

Index Scan using analyses_sampleset_b1d48620 on analyses_sampleset t24 (cost=0.29..2.51 rows=1 width=21) (actual time=0.001..0.002 rows=5 loops=2,400)

  • Index Cond: (patient_case_id = 19,458)
50. 12.000 12.000 ↓ 0.0 0 12,000

Index Scan using analyses_sample_2c3d5d28 on analyses_sample t25 (cost=0.29..2.51 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=12,000)

  • Index Cond: (sample_set_id = t24.id)
51. 12.000 12.000 ↑ 1.0 1 12,000

Index Scan using kits_kit_pkey on kits_kit t26 (cost=0.29..2.51 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=12,000)

  • Index Cond: (id = t24.kit_id)
52. 12.000 60.000 ↑ 1.0 1 12,000

Nested Loop Left Join (cost=0.86..4.25 rows=1 width=17) (actual time=0.004..0.005 rows=1 loops=12,000)

53. 0.000 36.000 ↑ 1.0 1 12,000

Nested Loop Left Join (cost=0.57..2.85 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=12,000)

54. 0.000 24.000 ↑ 1.0 1 12,000

Nested Loop Left Join (cost=0.42..2.66 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=12,000)

55. 12.000 12.000 ↑ 1.0 1 12,000

Index Scan using patients_samplesethandling_2c3d5d28 on patients_samplesethandling t27 (cost=0.28..2.50 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=12,000)

  • Index Cond: (sample_set_id = t24.id)
56. 12.000 12.000 ↑ 1.0 1 12,000

Index Only Scan using patients_distributioncompany_pkey on patients_distributioncompany t28 (cost=0.14..0.16 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12,000)

  • Index Cond: (company_ptr_id = t27.distribution_company_id)
  • Heap Fetches: 12,000
57. 12.000 12.000 ↑ 1.0 1 12,000

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

  • Index Cond: (partner_ptr_id = t28.company_ptr_id)
  • Heap Fetches: 12,000
58. 12.000 12.000 ↑ 1.0 1 12,000

Index Scan using patients_partner_id_8c95e9cc_uniq on patients_partner t30 (cost=0.29..1.40 rows=1 width=17) (actual time=0.001..0.001 rows=1 loops=12,000)

  • Index Cond: (id = t29.partner_ptr_id)
59. 24.000 24.000 ↓ 5.0 5 12,000

Index Scan using analyses_sampleset_b1d48620 on analyses_sampleset t31 (cost=0.29..2.51 rows=1 width=8) (actual time=0.001..0.002 rows=5 loops=12,000)

  • Index Cond: (patient_case_id = 19,458)
60. 60.000 60.000 ↑ 1.0 1 60,000

Index Scan using patients_samplesethandling_2c3d5d28 on patients_samplesethandling t32 (cost=0.28..2.50 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=60,000)

  • Index Cond: (sample_set_id = t31.id)
  • Filter: ((ended_at IS NULL) AND (started_at IS NOT NULL) AND (distribution_company_id = 28,279))
Planning time : 24.448 ms
Execution time : 241.119 ms