explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HEPo

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 52,290.461 ↑ 1.0 20 1

Limit (cost=72,290.85..72,290.92 rows=20 width=482) (actual time=52,290.446..52,290.461 rows=20 loops=1)

  • Buffers: shared hit=5052070 read=14763
2. 0.108 52,290.445 ↑ 12.4 20 1

Sort (cost=72,290.85..72,290.98 rows=249 width=482) (actual time=52,290.443..52,290.445 rows=20 loops=1)

  • Sort Key: (max(foo.sample_date)) DESC
  • Sort Method: top-N heapsort Memory: 38kB
  • Buffers: shared hit=5052070 read=14763
3. 5.763 52,290.337 ↑ 2.7 93 1

GroupAggregate (cost=71,773.11..72,288.87 rows=249 width=482) (actual time=52,284.706..52,290.337 rows=93 loops=1)

  • Group Key: foo.mr_no, foo.patient_name, foo.center_id, foo.collection_center, foo.visit_id, foo.registration_status, foo.billing_status, foo.source_visit_id, foo.source_pres_doctor
  • Filter: (((max(foo.sample_date))::date >= '2020-02-11'::date) AND ((max(foo.sample_date))::date <= '2020-02-11'::date))
  • Buffers: shared hit=5052070 read=14763
4. 0.597 52,284.574 ↑ 11.3 220 1

Sort (cost=71,773.11..71,774.36 rows=2,494 width=401) (actual time=52,284.549..52,284.574 rows=220 loops=1)

  • Sort Key: foo.mr_no, foo.patient_name, foo.center_id, foo.visit_id, foo.registration_status, foo.billing_status, foo.source_visit_id, foo.source_pres_doctor
  • Sort Method: quicksort Memory: 83kB
  • Buffers: shared hit=5052070 read=14763
5. 0.072 52,283.977 ↑ 11.3 220 1

Subquery Scan on foo (cost=71,721.28..71,744.97 rows=2,494 width=401) (actual time=52,283.638..52,283.977 rows=220 loops=1)

  • Buffers: shared hit=5052070 read=14763
6. 0.239 52,283.905 ↑ 11.3 220 1

Unique (cost=71,721.28..71,737.49 rows=2,494 width=401) (actual time=52,283.636..52,283.905 rows=220 loops=1)

  • Buffers: shared hit=5052070 read=14763
7. 1.886 52,283.666 ↑ 6.6 379 1

Sort (cost=71,721.28..71,722.52 rows=2,494 width=401) (actual time=52,283.634..52,283.666 rows=379 loops=1)

  • Sort Key: isr.mr_no, isr.patient_name, dd.ddept_id, dd.ddept_name, tp1.pat_id, sc.transfer_batch_id, isr.visit_id, (CASE WHEN (isr.visit_id IS NOT NULL) THEN 'C'::text ELSE 'P'::text END), sc.sample_date, isr.billing_status, tp.pat_id, ((SubPlan 1))
  • Sort Method: quicksort Memory: 125kB
  • Buffers: shared hit=5052070 read=14763
8. 1.125 52,281.780 ↑ 6.6 379 1

Hash Join (cost=1,697.54..71,693.13 rows=2,494 width=401) (actual time=42,009.322..52,281.780 rows=379 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (dd.ddept_id)::text)
  • Buffers: shared hit=5052070 read=14763
9. 0.321 52,268.511 ↑ 6.6 379 1

Nested Loop (cost=1,696.46..51,287.23 rows=2,494 width=138) (actual time=42,009.243..52,268.511 rows=379 loops=1)

  • Buffers: shared hit=5046889 read=14763
10. 0.485 52,264.779 ↑ 6.6 379 1

Nested Loop (cost=1,696.40..51,137.55 rows=2,494 width=136) (actual time=42,009.227..52,264.779 rows=379 loops=1)

  • Buffers: shared hit=5045752 read=14763
11. 47.182 52,262.399 ↑ 7.7 379 1

Nested Loop (cost=1,696.32..50,807.81 rows=2,921 width=140) (actual time=42,009.079..52,262.399 rows=379 loops=1)

  • Buffers: shared hit=5044224 read=14763
12. 382.936 49,943.067 ↓ 44.3 454,430 1

Nested Loop (cost=1,696.23..49,636.62 rows=10,255 width=127) (actual time=108.929..49,943.067 rows=454,430 loops=1)

  • Buffers: shared hit=3223469 read=14747
13. 471.469 46,353.851 ↓ 5.3 458,040 1

Nested Loop (cost=1,696.12..32,543.30 rows=87,040 width=112) (actual time=108.897..46,353.851 rows=458,040 loops=1)

  • Buffers: shared hit=933431 read=14554
14. 49.535 311.662 ↓ 40.6 146,060 1

Nested Loop (cost=1,696.01..10,028.60 rows=3,600 width=90) (actual time=100.764..311.662 rows=146,060 loops=1)

  • Buffers: shared hit=8961 read=987
15. 0.025 0.025 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm1 (cost=0.00..4.16 rows=1 width=22) (actual time=0.010..0.025 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC Deira'::text)
  • Rows Removed by Filter: 45
  • Buffers: shared hit=4
16. 162.371 262.102 ↓ 12.3 146,060 1

Bitmap Heap Scan on incoming_sample_registration isr (cost=1,696.01..9,988.95 rows=11,830 width=76) (actual time=100.750..262.102 rows=146,060 loops=1)

  • Recheck Cond: ((source_center_id = hcm1.center_id) AND (center_id = 12))
  • Filter: ((incoming_source_type)::text = 'C'::text)
  • Heap Blocks: exact=8957
  • Buffers: shared hit=8957 read=987
17. 0.387 99.731 ↓ 0.0 0 1

BitmapAnd (cost=1,696.01..1,696.01 rows=11,835 width=0) (actual time=99.731..99.731 rows=0 loops=1)

  • Buffers: shared read=987
18. 81.312 81.312 ↓ 4.6 148,188 1

Bitmap Index Scan on idx_incoming_sample_registration_sci (cost=0.00..266.37 rows=32,193 width=0) (actual time=81.311..81.312 rows=148,188 loops=1)

  • Index Cond: (source_center_id = hcm1.center_id)
  • Buffers: shared read=477
19. 18.032 18.032 ↑ 1.0 165,230 1

Bitmap Index Scan on incoming_registration__center_idx (cost=0.00..1,420.71 rows=165,749 width=0) (actual time=18.032..18.032 rows=165,230 loops=1)

  • Index Cond: (center_id = 12)
  • Buffers: shared read=510
20. 45,570.720 45,570.720 ↑ 8.0 3 146,060

Index Scan using pat_id_index on tests_prescribed tp1 (cost=0.11..6.18 rows=24 width=31) (actual time=0.243..0.312 rows=3 loops=146,060)

  • Index Cond: ((pat_id)::text = (isr.incoming_visit_id)::text)
  • Buffers: shared hit=924470 read=13567
21. 3,206.280 3,206.280 ↑ 1.0 1 458,040

Index Scan using tp_out_des_idx on tests_prescribed tp (cost=0.11..0.19 rows=1 width=23) (actual time=0.007..0.007 rows=1 loops=458,040)

  • Index Cond: (outsource_dest_prescribed_id = tp1.prescribed_id)
  • Filter: ((conducted)::text <> ALL ('{X,RAS}'::text[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2290038 read=193
22. 2,272.150 2,272.150 ↓ 0.0 0 454,430

Index Scan using sample_collection_id_pkey on sample_collection sc (cost=0.09..0.11 rows=1 width=21) (actual time=0.005..0.005 rows=0 loops=454,430)

  • Index Cond: (sample_collection_id = tp.sample_collection_id)
  • Filter: ((sample_date)::date >= '2020-02-11'::date)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1820755 read=16
23. 1.895 1.895 ↑ 1.0 1 379

Index Scan using sample_collection_id_pkey on sample_collection sc1 (cost=0.09..0.11 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=379)

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
  • Filter: (sample_receive_status = 'R'::bpchar)
  • Buffers: shared hit=1528
24. 3.411 3.411 ↑ 1.0 1 379

Index Scan using diagnostics_pkey on diagnostics d (cost=0.06..0.06 rows=1 width=18) (actual time=0.009..0.009 rows=1 loops=379)

  • Index Cond: ((test_id)::text = (tp1.test_id)::text)
  • Buffers: shared hit=1137
25. 0.007 0.016 ↑ 1.0 12 1

Hash (cost=1.04..1.04 rows=12 width=23) (actual time=0.016..0.016 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
26. 0.009 0.009 ↑ 1.0 12 1

Seq Scan on diagnostics_departments dd (cost=0.00..1.04 rows=12 width=23) (actual time=0.006..0.009 rows=12 loops=1)

  • Buffers: shared hit=1
27.          

SubPlan (for Hash Join)

28. 0.758 12.128 ↑ 1.0 1 379

Nested Loop (cost=0.17..8.18 rows=1 width=22) (actual time=0.026..0.032 rows=1 loops=379)

  • Buffers: shared hit=5180
29. 8.717 8.717 ↑ 1.0 1 379

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.11..4.12 rows=1 width=8) (actual time=0.017..0.023 rows=1 loops=379)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
  • Buffers: shared hit=4043
30. 2.653 2.653 ↑ 1.0 1 379

Index Scan using doctors_pkey on doctors doc (cost=0.06..4.06 rows=1 width=30) (actual time=0.007..0.007 rows=1 loops=379)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
  • Buffers: shared hit=1137
Planning time : 6.837 ms
Execution time : 52,290.991 ms