explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4qfy

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 7,906.882 ↑ 1.0 20 1

Limit (cost=1,664,855.00..1,664,885.20 rows=20 width=602) (actual time=7,906.690..7,906.882 rows=20 loops=1)

2. 0.267 7,906.881 ↑ 9.5 21 1

GroupAggregate (cost=1,664,853.49..1,665,155.42 rows=200 width=602) (actual time=7,906.680..7,906.881 rows=21 loops=1)

3. 0.149 7,906.614 ↑ 11.6 24 1

Sort (cost=1,664,853.49..1,664,854.19 rows=279 width=602) (actual time=7,906.612..7,906.614 rows=24 loops=1)

  • Sort Key: foo.sample_date_time, foo.sample_sno, foo.sample_assertion_batch, foo.sample_type, foo.sample_date, foo.collection_center, foo.mr_no, foo.patient_id, foo.sample_qty, foo.ih_name, foo.sample_status, foo.assertion_time, foo.rejected_time, foo.conducted, foo.sample_type_id, foo.collection_center_id, foo.visit_type, foo.center_id, foo.sample_collection_id, foo.patient_name, foo.outsource_dest_id, foo.outsource_name, foo.orig_sample_no, foo.transfer_time, foo.transfer_other_details, foo.receipt_time, foo.receipt_other_details, foo.bill_status, foo.charge_head
  • Sort Method: quicksort Memory: 45kB
4. 0.011 7,906.465 ↑ 4.6 61 1

Subquery Scan on foo (cost=1,664,695.69..1,664,842.16 rows=279 width=602) (actual time=7,906.436..7,906.465 rows=61 loops=1)

5. 0.167 7,906.454 ↑ 4.6 61 1

HashAggregate (cost=1,664,695.69..1,664,839.37 rows=279 width=366) (actual time=7,906.435..7,906.454 rows=61 loops=1)

6. 3.462 7,906.287 ↑ 4.2 67 1

Hash Join (cost=620,214.91..1,664,669.88 rows=279 width=366) (actual time=7,860.182..7,906.287 rows=67 loops=1)

  • Hash Cond: (sc.sample_type_id = st.sample_type_id)
7. 0.062 7,902.686 ↑ 4.2 67 1

Nested Loop (cost=620,113.81..1,664,424.04 rows=279 width=351) (actual time=7,859.755..7,902.686 rows=67 loops=1)

8. 0.017 7,902.155 ↑ 4.2 67 1

Nested Loop (cost=620,113.25..1,663,976.87 rows=279 width=363) (actual time=7,859.738..7,902.155 rows=67 loops=1)

9. 0.048 7,901.669 ↑ 4.2 67 1

Nested Loop Left Join (cost=620,112.69..1,663,367.18 rows=279 width=353) (actual time=7,859.724..7,901.669 rows=67 loops=1)

10. 0.051 7,901.621 ↑ 4.2 67 1

Nested Loop Left Join (cost=620,112.26..1,662,993.79 rows=279 width=348) (actual time=7,859.722..7,901.621 rows=67 loops=1)

11. 0.037 7,901.570 ↑ 4.2 67 1

Hash Left Join (cost=620,111.83..1,662,372.03 rows=279 width=348) (actual time=7,859.718..7,901.570 rows=67 loops=1)

  • Hash Cond: (pr.collection_center_id = scc.collection_center_id)
12. 0.042 7,901.530 ↑ 4.2 67 1

Nested Loop (cost=620,110.80..1,662,367.17 rows=279 width=339) (actual time=7,859.708..7,901.530 rows=67 loops=1)

13. 0.029 7,901.086 ↑ 4.5 67 1

Hash Left Join (cost=620,110.36..1,661,617.32 rows=299 width=337) (actual time=7,859.692..7,901.086 rows=67 loops=1)

  • Hash Cond: ((isr.orig_lab_name)::text = (ih.hospital_id)::text)
14. 0.017 7,901.049 ↑ 4.5 67 1

Nested Loop Left Join (cost=620,108.95..1,661,611.81 rows=299 width=334) (actual time=7,859.679..7,901.049 rows=67 loops=1)

  • Join Filter: ((isr.incoming_visit_id)::text = (isrd.incoming_visit_id)::text)
15. 0.050 7,900.898 ↑ 4.5 67 1

Hash Left Join (cost=620,108.53..1,661,227.76 rows=299 width=317) (actual time=7,859.670..7,900.898 rows=67 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
16. 0.058 7,900.838 ↑ 4.5 67 1

Nested Loop Left Join (cost=620,107.05..1,661,222.17 rows=299 width=321) (actual time=7,859.647..7,900.838 rows=67 loops=1)

17. 0.041 7,900.244 ↑ 4.5 67 1

Hash Join (cost=620,106.62..1,660,809.76 rows=299 width=288) (actual time=7,859.625..7,900.244 rows=67 loops=1)

  • Hash Cond: ((d.ddept_id)::text = (diagnostics_departments.ddept_id)::text)
18. 0.373 7,900.195 ↑ 4.5 67 1

Hash Left Join (cost=620,105.35..1,660,804.37 rows=299 width=275) (actual time=7,859.609..7,900.195 rows=67 loops=1)

  • Hash Cond: ((sc.patient_id)::text = (isr.incoming_visit_id)::text)
  • Filter: (COALESCE(pr.center_id, isr.center_id) = 13)
  • Rows Removed by Filter: 327
19. 0.176 7,806.463 ↑ 151.9 394 1

Nested Loop Left Join (cost=610,718.95..1,650,452.12 rows=59,833 width=228) (actual time=7,763.166..7,806.463 rows=394 loops=1)

20. 0.263 7,803.923 ↑ 151.9 394 1

Hash Join (cost=610,718.51..1,369,466.47 rows=59,833 width=187) (actual time=7,763.138..7,803.923 rows=394 loops=1)

  • Hash Cond: ((COALESCE(tp.test_id, tpr.test_id))::text = (d.test_id)::text)
21. 1,454.477 7,802.383 ↑ 151.9 394 1

Merge Right Join (cost=610,168.06..1,368,018.52 rows=59,833 width=154) (actual time=7,761.851..7,802.383 rows=394 loops=1)

  • Merge Cond: (tp.sample_collection_id = (CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END))
  • Filter: ((CASE WHEN (sc.sample_status = 'R'::bpchar) THEN tpr.conducted ELSE tp.conducted END)::text = ANY ('{N,NRN}'::text[]))
  • Rows Removed by Filter: 72
22. 2,428.898 2,428.898 ↑ 1.2 6,466,843 1

Index Scan using idx_tests_prescribed_sample_collection_id on tests_prescribed tp (cost=0.43..585,468.89 rows=7,551,672 width=19) (actual time=0.008..2,428.898 rows=6,466,843 loops=1)

23. 0.219 3,919.008 ↑ 284.3 466 1

Sort (cost=610,167.63..610,498.89 rows=132,504 width=145) (actual time=3,918.942..3,919.008 rows=466 loops=1)

  • Sort Key: (CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END)
  • Sort Method: quicksort Memory: 70kB
24. 505.979 3,918.789 ↑ 515.6 257 1

Hash Left Join (cost=550,887.81..598,894.40 rows=132,504 width=145) (actual time=3,411.390..3,918.789 rows=257 loops=1)

  • Hash Cond: (CASE WHEN (sc.sample_status = 'R'::bpchar) THEN sr.test_prescribed_id ELSE sc.sample_collection_id END = tpr.prescribed_id)
25. 0.762 598.371 ↑ 515.6 257 1

Hash Left Join (cost=143,117.19..144,767.40 rows=132,504 width=130) (actual time=596.637..598.371 rows=257 loops=1)

  • Hash Cond: (sc.outsource_dest_id = dom.outsource_dest_id)
  • Filter: ((dom.outsource_dest_type IS NULL) OR (dom.outsource_dest_type <> 'C'::bpchar))
  • Rows Removed by Filter: 3891
26. 3.283 597.514 ↑ 36.3 4,148 1

Hash Right Join (cost=143,106.35..143,867.34 rows=150,471 width=105) (actual time=596.510..597.514 rows=4,148 loops=1)

  • Hash Cond: (sr.sample_collection_id = sc.sample_collection_id)
27. 1.331 1.331 ↑ 1.0 25,665 1

Seq Scan on sample_rejections sr (cost=0.00..395.65 rows=25,665 width=8) (actual time=0.003..1.331 rows=25,665 loops=1)

28. 1.138 592.900 ↑ 36.3 4,148 1

Hash (cost=141,225.46..141,225.46 rows=150,471 width=101) (actual time=592.900..592.900 rows=4,148 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 421kB
29. 591.762 591.762 ↑ 36.3 4,148 1

Seq Scan on sample_collection sc (cost=0.00..141,225.46 rows=150,471 width=101) (actual time=368.578..591.762 rows=4,148 loops=1)

  • Filter: ((sample_status = 'C'::bpchar) AND (sample_receive_status = 'R'::bpchar) AND (sample_status = ANY ('{C,A,R}'::bpchar[])) AND ((sample_date)::date >= '2018-12-29'::date))
  • Rows Removed by Filter: 3095660
30. 0.014 0.095 ↑ 1.0 67 1

Hash (cost=10.00..10.00 rows=67 width=31) (actual time=0.095..0.095 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
31. 0.016 0.081 ↑ 1.0 67 1

Hash Left Join (cost=6.84..10.00 rows=67 width=31) (actual time=0.054..0.081 rows=67 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (hcm.center_id)::text)
32. 0.022 0.047 ↑ 1.0 67 1

Hash Left Join (cost=2.33..4.84 rows=67 width=28) (actual time=0.033..0.047 rows=67 loops=1)

  • Hash Cond: ((dom.outsource_dest)::text = (om.oh_id)::text)
33. 0.003 0.003 ↑ 1.0 67 1

Seq Scan on diag_outsource_master dom (cost=0.00..1.67 rows=67 width=15) (actual time=0.001..0.003 rows=67 loops=1)

34. 0.012 0.022 ↑ 1.0 59 1

Hash (cost=1.59..1.59 rows=59 width=24) (actual time=0.022..0.022 rows=59 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
35. 0.010 0.010 ↑ 1.0 59 1

Seq Scan on outhouse_master om (cost=0.00..1.59 rows=59 width=24) (actual time=0.003..0.010 rows=59 loops=1)

36. 0.008 0.018 ↑ 1.0 23 1

Hash (cost=4.23..4.23 rows=23 width=16) (actual time=0.018..0.018 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
37. 0.010 0.010 ↑ 1.0 23 1

Seq Scan on hospital_center_master hcm (cost=0.00..4.23 rows=23 width=16) (actual time=0.002..0.010 rows=23 loops=1)

38. 1,318.187 2,814.439 ↓ 1.0 7,554,345 1

Hash (cost=276,500.72..276,500.72 rows=7,551,672 width=15) (actual time=2,814.439..2,814.439 rows=7,554,345 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 46133kB
39. 1,496.252 1,496.252 ↓ 1.0 7,554,345 1

Seq Scan on tests_prescribed tpr (cost=0.00..276,500.72 rows=7,551,672 width=15) (actual time=0.002..1,496.252 rows=7,554,345 loops=1)

40. 0.438 1.277 ↑ 1.0 1,798 1

Hash (cost=527.98..527.98 rows=1,798 width=49) (actual time=1.277..1.277 rows=1,798 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 143kB
41. 0.839 0.839 ↑ 1.0 1,798 1

Seq Scan on diagnostics d (cost=0.00..527.98 rows=1,798 width=49) (actual time=0.001..0.839 rows=1,798 loops=1)

42. 2.364 2.364 ↑ 1.0 1 394

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..4.69 rows=1 width=41) (actual time=0.006..0.006 rows=1 loops=394)

  • Index Cond: ((patient_id)::text = (sc.patient_id)::text)
43. 45.840 93.359 ↑ 1.0 225,307 1

Hash (cost=6,570.07..6,570.07 rows=225,307 width=62) (actual time=93.359..93.359 rows=225,307 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 21046kB
44. 47.519 47.519 ↑ 1.0 225,307 1

Seq Scan on incoming_sample_registration isr (cost=0.00..6,570.07 rows=225,307 width=62) (actual time=0.004..47.519 rows=225,307 loops=1)

45. 0.001 0.008 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
46. 0.007 0.007 ↑ 1.0 12 1

Seq Scan on diagnostics_departments (cost=0.00..1.12 rows=12 width=23) (actual time=0.002..0.007 rows=12 loops=1)

47. 0.536 0.536 ↑ 1.0 1 67

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..1.37 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=67)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
48. 0.004 0.010 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=14) (actual time=0.010..0.010 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
49. 0.006 0.006 ↑ 1.0 21 1

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=14) (actual time=0.003..0.006 rows=21 loops=1)

50. 0.134 0.134 ↓ 0.0 0 67

Index Scan using incoming_sample_registration_details_pkey on incoming_sample_registration_details isrd (cost=0.42..1.27 rows=1 width=30) (actual time=0.002..0.002 rows=0 loops=67)

  • Index Cond: (tp.prescribed_id = prescribed_id)
51. 0.003 0.008 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=17) (actual time=0.008..0.008 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
52. 0.005 0.005 ↑ 1.0 18 1

Seq Scan on incoming_hospitals ih (cost=0.00..1.18 rows=18 width=17) (actual time=0.002..0.005 rows=18 loops=1)

53. 0.402 0.402 ↑ 1.0 1 67

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.45..2.50 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=67)

  • Index Cond: (COALESCE((tp.prescribed_id)::text, (tpr.prescribed_id)::text) = (activity_id)::text)
  • Filter: ((activity_code)::text = 'DIA'::text)
54. 0.001 0.003 ↑ 1.0 1 1

Hash (cost=1.01..1.01 rows=1 width=13) (actual time=0.003..0.003 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
55. 0.002 0.002 ↑ 1.0 1 1

Seq Scan on sample_collection_centers scc (cost=0.00..1.01 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=1)

56. 0.000 0.000 ↓ 0.0 0 67

Index Scan using tests_prescribed_pkey on tests_prescribed tp1 (cost=0.43..2.22 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=67)

  • Index Cond: (prescribed_id = isrd.source_test_prescribed)
57. 0.000 0.000 ↓ 0.0 0 67

Index Scan using sample_collection_id_pkey on sample_collection sc1 (cost=0.43..1.33 rows=1 width=13) (actual time=0.000..0.000 rows=0 loops=67)

  • Index Cond: (sample_collection_id = tp1.sample_collection_id)
58. 0.469 0.469 ↑ 1.0 1 67

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..2.18 rows=1 width=30) (actual time=0.007..0.007 rows=1 loops=67)

  • Index Cond: ((charge_id)::text = (bac.charge_id)::text)
59. 0.469 0.469 ↑ 1.0 1 67

Index Scan using bill_pkey on bill b (cost=0.56..1.59 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=67)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
60. 0.012 0.139 ↑ 1.0 49 1

Hash (cost=100.49..100.49 rows=49 width=19) (actual time=0.139..0.139 rows=49 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
61. 0.127 0.127 ↑ 1.0 49 1

Seq Scan on sample_type st (cost=0.00..100.49 rows=49 width=19) (actual time=0.003..0.127 rows=49 loops=1)