explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cr1D

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 4,713.107 ↑ 1.0 20 1

Limit (cost=72,290.75..72,290.82 rows=20 width=482) (actual time=4,713.097..4,713.107 rows=20 loops=1)

  • Buffers: shared hit=5066822
2. 0.051 4,713.096 ↑ 12.4 20 1

Sort (cost=72,290.75..72,290.88 rows=249 width=482) (actual time=4,713.095..4,713.096 rows=20 loops=1)

  • Sort Key: (max(foo.sample_date)) DESC
  • Sort Method: top-N heapsort Memory: 38kB
  • Buffers: shared hit=5066822
3. 1.794 4,713.045 ↑ 2.7 93 1

GroupAggregate (cost=71,773.01..72,288.77 rows=249 width=482) (actual time=4,711.306..4,713.045 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=5066822
4. 0.286 4,711.251 ↑ 11.3 220 1

Sort (cost=71,773.01..71,774.26 rows=2,494 width=401) (actual time=4,711.239..4,711.251 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=5066822
5. 0.035 4,710.965 ↑ 11.3 220 1

Subquery Scan on foo (cost=71,721.18..71,744.87 rows=2,494 width=401) (actual time=4,710.808..4,710.965 rows=220 loops=1)

  • Buffers: shared hit=5066822
6. 0.110 4,710.930 ↑ 11.3 220 1

Unique (cost=71,721.18..71,737.39 rows=2,494 width=401) (actual time=4,710.806..4,710.930 rows=220 loops=1)

  • Buffers: shared hit=5066822
7. 0.867 4,710.820 ↑ 6.6 379 1

Sort (cost=71,721.18..71,722.42 rows=2,494 width=401) (actual time=4,710.805..4,710.820 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=5066822
8. 0.412 4,709.953 ↑ 6.6 379 1

Hash Join (cost=1,697.54..71,693.03 rows=2,494 width=401) (actual time=4,358.871..4,709.953 rows=379 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (dd.ddept_id)::text)
  • Buffers: shared hit=5066822
9. 0.100 4,704.222 ↑ 6.6 379 1

Nested Loop (cost=1,696.46..51,287.13 rows=2,494 width=138) (actual time=4,358.807..4,704.222 rows=379 loops=1)

  • Buffers: shared hit=5061641
10. 0.209 4,702.606 ↑ 6.6 379 1

Nested Loop (cost=1,696.40..51,137.45 rows=2,494 width=136) (actual time=4,358.792..4,702.606 rows=379 loops=1)

  • Buffers: shared hit=5060504
11. 0.000 4,701.639 ↑ 7.7 379 1

Nested Loop (cost=1,696.32..50,807.71 rows=2,921 width=140) (actual time=4,358.783..4,701.639 rows=379 loops=1)

  • Buffers: shared hit=5058976
12. 184.450 3,415.379 ↓ 44.3 454,430 1

Nested Loop (cost=1,696.23..49,636.53 rows=10,255 width=127) (actual time=25.701..3,415.379 rows=454,430 loops=1)

  • Buffers: shared hit=3238205
13. 249.052 1,856.809 ↓ 5.3 458,040 1

Nested Loop (cost=1,696.12..32,543.25 rows=87,040 width=112) (actual time=25.681..1,856.809 rows=458,040 loops=1)

  • Buffers: shared hit=947974
14. 23.029 147.157 ↓ 40.6 146,060 1

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

  • Buffers: shared hit=9948
15. 0.020 0.020 ↑ 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.020 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC Deira'::text)
  • Rows Removed by Filter: 45
  • Buffers: shared hit=4
16. 100.448 124.108 ↓ 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=25.638..124.108 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=9944
17. 0.743 23.660 ↓ 0.0 0 1

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

  • Buffers: shared hit=987
18. 8.734 8.734 ↓ 4.6 148,187 1

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

  • Index Cond: (source_center_id = hcm1.center_id)
  • Buffers: shared hit=477
19. 14.183 14.183 ↑ 1.0 165,229 1

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

  • Index Cond: (center_id = 12)
  • Buffers: shared hit=510
20. 1,460.600 1,460.600 ↑ 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.008..0.010 rows=3 loops=146,060)

  • Index Cond: ((pat_id)::text = (isr.incoming_visit_id)::text)
  • Buffers: shared hit=938026
21. 1,374.120 1,374.120 ↑ 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.003..0.003 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=2290231
22. 1,363.290 1,363.290 ↓ 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.003..0.003 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=1820771
23. 0.758 0.758 ↑ 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.002..0.002 rows=1 loops=379)

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

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

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

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

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

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

  • Buffers: shared hit=1
27.          

SubPlan (for Hash Join)

28. 0.379 5.306 ↑ 1.0 1 379

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

  • Buffers: shared hit=5180
29. 3.790 3.790 ↑ 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.007..0.010 rows=1 loops=379)

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

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

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
  • Buffers: shared hit=1137
Planning time : 4.399 ms
Execution time : 4,713.605 ms