explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rglg

Settings
# exclusive inclusive rows x rows loops node
1. 0.177 63,596.476 ↑ 282.8 4 1

Nested Loop Left Join (cost=1,538,341.59..3,918,203.95 rows=1,131 width=1,096) (actual time=58,623.625..63,596.476 rows=4 loops=1)

  • -> Hash Join (cost=4.08..8.92 rows=1 width=218) (actual time=0.163..0.163 rows=0 loops=4)Hash Cond: (hcm.center_id = (dom.outsource_dest)::integer)
2. 0.037 63,590.833 ↑ 282.8 4 1

Hash Join (cost=1,538,341.16..3,884,768.07 rows=1,131 width=605) (actual time=58,618.355..63,590.833 rows=4 loops=1)

  • Hash Cond: ((tp.test_id)::text = (d.test_id)::text)
3. 9,708.115 63,587.076 ↑ 282.8 4 1

Hash Left Join (cost=1,538,249.22..3,884,673.16 rows=1,131 width=572) (actual time=58,614.620..63,587.076 rows=4 loops=1)

  • Hash Cond: (tp.report_id = tvr.report_id)
  • Filter: ((tp.c_mod_time >= '2020-07-01 18:22:00'::timestamp without time zone) OR (pr.mod_time >= '2020-07-01 18:22:00'::timestamp without time zone) OR (pd.mod_time >= '2020-07-01 18:22:00'::timestamp without time zone) OR (tvr.report_date >= '2020-07-01 18:22:00'::timestamp without time zone))
  • Rows Removed by Filter: 10,016,856
4. 9,259.257 49,522.155 ↑ 1.1 10,016,860 1

Hash Join (cost=850,972.01..2,206,468.76 rows=11,374,537 width=175) (actual time=17,809.138..49,522.155 rows=10,016,860 loops=1)

  • Hash Cond: ((tp.mr_no)::text = (pd.mr_no)::text)
5. 9,503.751 35,147.842 ↑ 1.1 10,016,860 1

Hash Join (cost=567,640.28..1,453,968.83 rows=11,374,537 width=116) (actual time=12,693.397..35,147.842 rows=10,016,860 loops=1)

  • Hash Cond: ((tp.pat_id)::text = (pr.patient_id)::text)
6. 12,957.048 12,957.048 ↑ 1.0 11,372,537 1

Seq Scan on tests_prescribed tp (cost=0.00..480,658.37 rows=11,374,537 width=85) (actual time=0.688..12,957.048 rows=11,372,537 loops=1)

7. 2,083.191 12,687.043 ↓ 1.0 7,375,172 1

Hash (cost=410,709.90..410,709.90 rows=7,371,390 width=46) (actual time=12,687.043..12,687.043 rows=7,375,172 loops=1)

  • Buckets: 1,048,576 Batches: 16 Memory Usage: 44,194kB
8. 10,603.852 10,603.852 ↓ 1.0 7,375,172 1

Seq Scan on patient_registration pr (cost=0.00..410,709.90 rows=7,371,390 width=46) (actual time=0.016..10,603.852 rows=7,375,172 loops=1)

9. 1,052.840 5,115.056 ↑ 1.1 2,769,345 1

Hash (cost=205,094.88..205,094.88 rows=3,105,188 width=74) (actual time=5,115.056..5,115.056 rows=2,769,345 loops=1)

  • Buckets: 524,288 Batches: 8 Memory Usage: 43,084kB
10. 4,062.216 4,062.216 ↑ 1.1 2,769,345 1

Seq Scan on patient_details pd (cost=0.00..205,094.88 rows=3,105,188 width=74) (actual time=0.046..4,062.216 rows=2,769,345 loops=1)

11. 1,329.557 4,356.806 ↓ 1.0 7,287,924 1

Hash (cost=190,508.32..190,508.32 rows=7,287,832 width=429) (actual time=4,356.806..4,356.806 rows=7,287,924 loops=1)

  • Buckets: 131,072 Batches: 64 Memory Usage: 6,398kB
12. 3,027.249 3,027.249 ↓ 1.0 7,287,924 1

Seq Scan on test_visit_reports tvr (cost=0.00..190,508.32 rows=7,287,832 width=429) (actual time=0.813..3,027.249 rows=7,287,924 loops=1)

13. 1.118 3.720 ↓ 1.0 1,918 1

Hash (cost=68.08..68.08 rows=1,908 width=41) (actual time=3.720..3.720 rows=1,918 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 154kB
14. 2.602 2.602 ↓ 1.0 1,918 1

Seq Scan on diagnostics d (cost=0.00..68.08 rows=1,908 width=41) (actual time=0.490..2.602 rows=1,918 loops=1)

15. 0.076 0.076 ↑ 1.0 1 4

Index Scan using sample_collection_id_pkey on sample_collection sc (cost=0.43..0.49 rows=1 width=20) (actual time=0.019..0.019 rows=1 loops=4)

  • Index Cond: (sample_collection_id = tp.sample_collection_id)
16.          

SubPlan (for Nested Loop Left Join)

17. 1.032 1.032 ↑ 1.0 1 4

Seq Scan on hospital_center_master (cost=0.00..4.64 rows=1 width=4) (actual time=0.228..0.258 rows=1 loops=4)

  • Filter: (center_id = pr.center_id)
  • Rows Removed by Filter: 37
18. 0.016 0.016 ↑ 1.0 1 4

Seq Scan on hospital_center_master hospital_center_master_1 (cost=0.00..4.64 rows=1 width=218) (actual time=0.004..0.004 rows=1 loops=4)

  • Filter: (center_id = pr.center_id)
  • Rows Removed by Filter: 37
19. 0.428 0.428 ↑ 1.0 1 4

Seq Scan on salutation_master (cost=0.00..1.26 rows=1 width=118) (actual time=0.105..0.107 rows=1 loops=4)

  • Filter: ((salutation_id)::text = (pd.salutation)::text)
  • Rows Removed by Filter: 20
20. 0.580 0.580 ↑ 1.0 1 4

Seq Scan on encounter_end_types (cost=0.00..1.14 rows=1 width=32) (actual time=0.143..0.145 rows=1 loops=4)

  • Filter: ((code)::text = (pr.encounter_end_type)::text)
  • Rows Removed by Filter: 7
21. 0.002 0.002 ↑ 51.0 1 1

Seq Scan on hospital_center_master hcm (cost=0.00..4.51 rows=51 width=222) (actual time=0.002..0.002 rows=1 loops=1)

22. 0.012 0.592 ↓ 0.0 0 4

Hash (cost=4.07..4.07 rows=1 width=27) (actual time=0.148..0.148 rows=0 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
23. 0.037 0.580 ↓ 0.0 0 4

Hash Join (cost=2.04..4.07 rows=1 width=27) (actual time=0.145..0.145 rows=0 loops=4)

  • Hash Cond: ((om.oh_id)::text = (dom.outsource_dest)::text)
24. 0.015 0.015 ↑ 1.5 50 3

Seq Scan on outhouse_master om (cost=0.00..1.74 rows=74 width=26) (actual time=0.003..0.005 rows=50 loops=3)

25. 0.008 0.528 ↓ 0.0 0 4

Hash (cost=2.03..2.03 rows=1 width=12) (actual time=0.132..0.132 rows=0 loops=4)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.520 0.520 ↓ 0.0 0 4

Seq Scan on diag_outsource_master dom (cost=0.00..2.03 rows=1 width=12) (actual time=0.129..0.130 rows=0 loops=4)

  • Filter: (outsource_dest_id = tp.outsource_dest_id)
  • Rows Removed by Filter: 82
27. 2.740 2.740 ↓ 0.0 0 4

Index Scan using tests_conducted_c_mod_time_idx on tests_conducted (cost=0.43..8.46 rows=1 width=2) (actual time=0.685..0.685 rows=0 loops=4)

  • Index Cond: (c_mod_time >= '2020-07-01 18:22:00'::timestamp without time zone)
  • Filter: (prescribed_id = tp.prescribed_id)
  • Rows Removed by Filter: 4
Planning time : 56.007 ms
Execution time : 63,597.214 ms