explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eO88

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 3,783.770 ↓ 0.0 0 1

Limit (cost=120,175.18..120,175.43 rows=20 width=482) (actual time=3,783.770..3,783.770 rows=0 loops=1)

  • Buffers: shared hit=4560705
2. 0.006 3,783.769 ↓ 0.0 0 1

Sort (cost=120,175.18..120,175.86 rows=274 width=482) (actual time=3,783.769..3,783.769 rows=0 loops=1)

  • Sort Key: (max(foo.sample_date)) DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=4560705
3. 0.002 3,783.763 ↓ 0.0 0 1

GroupAggregate (cost=117,336.42..120,164.08 rows=274 width=482) (actual time=3,783.763..3,783.763 rows=0 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=4560705
4. 0.010 3,783.761 ↓ 0.0 0 1

Sort (cost=117,336.42..117,343.26 rows=2,736 width=401) (actual time=3,783.761..3,783.761 rows=0 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: 25kB
  • Buffers: shared hit=4560705
5. 0.001 3,783.751 ↓ 0.0 0 1

Subquery Scan on foo (cost=117,063.95..117,180.23 rows=2,736 width=401) (actual time=3,783.751..3,783.751 rows=0 loops=1)

  • Buffers: shared hit=4560705
6. 0.000 3,783.750 ↓ 0.0 0 1

Unique (cost=117,063.95..117,152.87 rows=2,736 width=401) (actual time=3,783.750..3,783.750 rows=0 loops=1)

  • Buffers: shared hit=4560705
7. 0.012 3,783.750 ↓ 0.0 0 1

Sort (cost=117,063.95..117,070.79 rows=2,736 width=401) (actual time=3,783.750..3,783.750 rows=0 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: 25kB
  • Buffers: shared hit=4560705
8. 0.006 3,783.738 ↓ 0.0 0 1

Hash Join (cost=464.71..116,907.75 rows=2,736 width=401) (actual time=3,783.738..3,783.738 rows=0 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (dd.ddept_id)::text)
  • Buffers: shared hit=4560705
9. 0.001 3,783.714 ↓ 0.0 0 1

Nested Loop (cost=463.44..92,604.38 rows=2,736 width=138) (actual time=3,783.714..3,783.714 rows=0 loops=1)

  • Buffers: shared hit=4560704
10. 0.000 3,783.713 ↓ 0.0 0 1

Nested Loop (cost=463.16..91,797.23 rows=2,736 width=136) (actual time=3,783.713..3,783.713 rows=0 loops=1)

  • Buffers: shared hit=4560704
11. 156.478 3,783.713 ↓ 0.0 0 1

Nested Loop (cost=462.73..90,277.46 rows=3,207 width=140) (actual time=3,783.713..3,783.713 rows=0 loops=1)

  • Buffers: shared hit=4560704
12. 299.533 2,724.811 ↓ 40.1 451,212 1

Nested Loop (cost=462.30..84,901.83 rows=11,264 width=127) (actual time=8.004..2,724.811 rows=451,212 loops=1)

  • Buffers: shared hit=2752854
13. 248.315 1,515.644 ↓ 4.8 454,817 1

Nested Loop (cost=461.86..34,597.51 rows=95,099 width=112) (actual time=7.989..1,515.644 rows=454,817 loops=1)

  • Buffers: shared hit=934482
14. 21.663 107.809 ↓ 40.1 144,940 1

Nested Loop (cost=461.30..10,656.39 rows=3,613 width=90) (actual time=7.963..107.809 rows=144,940 loops=1)

  • Buffers: shared hit=9362
15. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on hospital_center_master hcm1 (cost=0.00..4.58 rows=1 width=22) (actual time=0.011..0.019 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC Deira'::text)
  • Rows Removed by Filter: 45
  • Buffers: shared hit=4
16. 79.356 86.127 ↓ 12.2 144,940 1

Bitmap Heap Scan on incoming_sample_registration isr (cost=461.30..10,533.09 rows=11,872 width=76) (actual time=7.949..86.127 rows=144,940 loops=1)

  • Recheck Cond: (source_center_id = hcm1.center_id)
  • Filter: ((center_id = 12) AND ((incoming_source_type)::text = 'C'::text))
  • Rows Removed by Filter: 1965
  • Heap Blocks: exact=8888
  • Buffers: shared hit=9358
17. 6.771 6.771 ↓ 4.6 146,905 1

Bitmap Index Scan on idx_incoming_sample_registration_sci (cost=0.00..458.33 rows=31,988 width=0) (actual time=6.771..6.771 rows=146,905 loops=1)

  • Index Cond: (source_center_id = hcm1.center_id)
  • Buffers: shared hit=470
18. 1,159.520 1,159.520 ↑ 8.7 3 144,940

Index Scan using pat_id_index_n on tests_prescribed tp1 (cost=0.56..6.37 rows=26 width=31) (actual time=0.006..0.008 rows=3 loops=144,940)

  • Index Cond: ((pat_id)::text = (isr.incoming_visit_id)::text)
  • Buffers: shared hit=925120
19. 909.634 909.634 ↑ 1.0 1 454,817

Index Scan using tp_out_des_idx_n on tests_prescribed tp (cost=0.43..0.52 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=454,817)

  • Index Cond: (outsource_dest_prescribed_id = tp1.prescribed_id)
  • Filter: ((conducted)::text <> ALL ('{X,RAS}'::text[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=1818372
20. 902.424 902.424 ↓ 0.0 0 451,212

Index Scan using sample_collection_id_pkey on sample_collection sc (cost=0.43..0.48 rows=1 width=21) (actual time=0.002..0.002 rows=0 loops=451,212)

  • 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=1807850
21. 0.000 0.000 ↓ 0.0 0

Index Scan using sample_collection_id_pkey on sample_collection sc1 (cost=0.43..0.47 rows=1 width=4) (never executed)

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
  • Filter: (sample_receive_status = 'R'::bpchar)
22. 0.000 0.000 ↓ 0.0 0

Index Scan using diagnostics_pkey on diagnostics d (cost=0.28..0.30 rows=1 width=18) (never executed)

  • Index Cond: ((test_id)::text = (tp1.test_id)::text)
23. 0.008 0.018 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=23) (actual time=0.018..0.018 rows=12 loops=1)

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

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

  • Buffers: shared hit=1
25.          

SubPlan (for Hash Join)

26. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.84..8.88 rows=1 width=22) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..4.58 rows=1 width=8) (never executed)

  • Index Cond: ((patient_id)::text = (tp.pat_id)::text)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using doctors_pkey on doctors doc (cost=0.28..4.30 rows=1 width=30) (never executed)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
Planning time : 4.719 ms
Execution time : 3,784.060 ms