explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CXaI

Settings
# exclusive inclusive rows x rows loops node
1. 78.990 13,881.989 ↑ 1.0 1 1

Aggregate (cost=395,049.55..395,049.56 rows=1 width=384) (actual time=13,881.989..13,881.989 rows=1 loops=1)

  • Buffers: shared hit=14,344,715
2. 19.321 13,802.999 ↓ 486.2 156,056 1

Append (cost=151,334.78..395,036.68 rows=321 width=2,288) (actual time=1,232.640..13,802.999 rows=156,056 loops=1)

  • Buffers: shared hit=14,344,715
3. 214.625 12,571.767 ↓ 471.6 136,299 1

Subquery Scan on aa (cost=151,334.78..163,861.84 rows=289 width=1,100) (actual time=1,232.639..12,571.767 rows=136,299 loops=1)

  • Buffers: shared hit=13,255,160
4. 8,103.351 12,084.544 ↓ 471.6 136,299 1

GroupAggregate (cost=151,334.78..162,554.12 rows=289 width=1,212) (actual time=1,232.620..12,084.544 rows=136,299 loops=1)

  • Group Key: ct.cln_collectiontxn_id, ppt.cln_patient_id, (CASE WHEN ((ct.collection_type)::text = ANY ('{CTY1,CTY5,CTY17,CTY22}'::text[])) THEN '收费'::text ELSE '退费'::text END), ((SubPlan 2))
  • Buffers: shared hit=12,711,149
5. 133.274 1,257.079 ↓ 473.4 136,820 1

Sort (cost=151,334.78..151,335.50 rows=289 width=376) (actual time=1,231.412..1,257.079 rows=136,820 loops=1)

  • Sort Key: ct.cln_collectiontxn_id, ppt.cln_patient_id, (CASE WHEN ((ct.collection_type)::text = ANY ('{CTY1,CTY5,CTY17,CTY22}'::text[])) THEN '收费'::text ELSE '退费'::text END), ((SubPlan 2))
  • Sort Method: quicksort Memory: 42,492kB
  • Buffers: shared hit=1,578,462
6. 113.491 1,123.805 ↓ 473.4 136,820 1

Nested Loop (cost=54,008.28..151,322.96 rows=289 width=376) (actual time=81.731..1,123.805 rows=136,820 loops=1)

  • Buffers: shared hit=1,578,462
7. 65.175 599.854 ↓ 473.4 136,820 1

Nested Loop (cost=54,007.85..149,564.52 rows=289 width=102) (actual time=81.706..599.854 rows=136,820 loops=1)

  • Buffers: shared hit=620,454
8. 60.716 264.771 ↓ 20.6 134,954 1

Hash Join (cost=54,007.42..139,831.46 rows=6,541 width=89) (actual time=81.685..264.771 rows=134,954 loops=1)

  • Hash Cond: (r.cln_patient_id = ppt.cln_patient_id)
  • Buffers: shared hit=78,020
9. 130.101 141.087 ↓ 1.0 134,954 1

Bitmap Heap Scan on cln_receipt r (cost=1,941.01..87,411.24 rows=134,784 width=57) (actual time=18.633..141.087 rows=134,954 loops=1)

  • Recheck Cond: (ptf_clinic_id = '2503888852'::bigint)
  • Heap Blocks: exact=54,241
  • Buffers: shared hit=54,768
10. 10.986 10.986 ↓ 1.0 135,013 1

Bitmap Index Scan on ndx_receipt_clinicid (cost=0.00..1,907.31 rows=134,784 width=0) (actual time=10.986..10.986 rows=135,013 loops=1)

  • Index Cond: (ptf_clinic_id = '2503888852'::bigint)
  • Buffers: shared hit=527
11. 15.975 62.968 ↓ 1.0 79,063 1

Hash (cost=51,101.18..51,101.18 rows=77,218 width=40) (actual time=62.968..62.968 rows=79,063 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 6,203kB
  • Buffers: shared hit=23,252
12. 41.561 46.993 ↓ 1.0 79,063 1

Bitmap Heap Scan on cln_patient ppt (cost=1,668.87..51,101.18 rows=77,218 width=40) (actual time=8.446..46.993 rows=79,063 loops=1)

  • Recheck Cond: (ptf_topclinic_id = '2503887478'::bigint)
  • Heap Blocks: exact=22,787
  • Buffers: shared hit=23,252
13. 5.432 5.432 ↓ 1.0 79,109 1

Bitmap Index Scan on ndx_patient_topclinicid_creatdt (cost=0.00..1,649.56 rows=77,218 width=0) (actual time=5.432..5.432 rows=79,109 loops=1)

  • Index Cond: (ptf_topclinic_id = '2503887478'::bigint)
  • Buffers: shared hit=465
14. 269.908 269.908 ↑ 1.0 1 134,954

Index Scan using ndx_vstrcpthistry_rcptid on cln_receipthistory rh (cost=0.43..1.48 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=134,954)

  • Index Cond: (cln_receipt_id = r.cln_receipt_id)
  • Filter: (((receipt_txn_status)::text = ANY ('{RHS1,RHS2}'::text[])) AND (data_top_id = '2503887478'::bigint))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=542,434
15. 273.640 273.640 ↑ 1.0 1 136,820

Index Scan using pk_cln_collectiontxn on cln_collectiontxn ct (cost=0.43..1.77 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=136,820)

  • Index Cond: (cln_collectiontxn_id = rh.cln_collectiontxn_id)
  • Buffers: shared hit=547,547
16.          

SubPlan (for Nested Loop)

17. 136.820 136.820 ↑ 1.0 1 136,820

Index Scan using pk_cln_user on cln_user (cost=0.29..4.30 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=136,820)

  • Index Cond: (cln_user_id = ct.txn_oper_id)
  • Buffers: shared hit=410,461
18.          

SubPlan (for GroupAggregate)

19. 136.299 408.897 ↑ 1.0 1 136,299

Aggregate (cost=4.46..4.47 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=136,299)

  • Buffers: shared hit=544,011
20. 272.598 272.598 ↑ 1.0 1 136,299

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_1 (cost=0.43..4.45 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=136,299)

  • Index Cond: (cln_collectiontxn_id = ct.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text <> 'PYMSYBX'::text)
  • Buffers: shared hit=544,011
21. 134.433 2,150.928 ↑ 1.0 1 134,433

Aggregate (cost=14.97..14.98 rows=1 width=32) (actual time=0.016..0.016 rows=1 loops=134,433)

  • Buffers: shared hit=2,535,369
22. 0.000 2,016.495 ↓ 0.0 0 134,433

Nested Loop Semi Join (cost=10.70..14.97 rows=1 width=5) (actual time=0.015..0.015 rows=0 loops=134,433)

  • Buffers: shared hit=2,535,369
23. 4.058 2,016.495 ↓ 0.0 0 134,433

Nested Loop (cost=10.27..14.41 rows=1 width=13) (actual time=0.015..0.015 rows=0 loops=134,433)

  • Buffers: shared hit=2,535,369
24. 4.058 1,747.629 ↑ 1.0 1 134,433

Nested Loop (cost=9.84..13.89 rows=1 width=21) (actual time=0.012..0.013 rows=1 loops=134,433)

  • Buffers: shared hit=2,004,783
25. 134.433 1,478.763 ↑ 1.0 1 134,433

Limit (cost=9.41..9.42 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=134,433)

  • Buffers: shared hit=1,474,382
26. 134.433 1,344.330 ↑ 1.0 1 134,433

Group (cost=9.41..9.42 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=134,433)

  • Group Key: rc_3.cln_collectiontxn_id, rc_3.operated_datetime
  • Buffers: shared hit=1,474,382
27. 134.433 1,209.897 ↑ 1.0 1 134,433

Sort (cost=9.41..9.42 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=134,433)

  • Sort Key: rc_3.cln_collectiontxn_id, rc_3.operated_datetime
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1,474,382
28. 4.042 1,075.464 ↑ 1.0 1 134,433

Nested Loop (cost=5.32..9.40 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=134,433)

  • Buffers: shared hit=1,474,382
29. 134.433 806.598 ↑ 1.0 1 134,433

Nested Loop (cost=4.89..8.93 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=134,433)

  • Buffers: shared hit=1,074,956
30. 0.000 403.299 ↑ 1.0 1 134,433

Unique (cost=4.46..4.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=134,433)

  • Buffers: shared hit=538,492
31. 134.433 403.299 ↑ 1.0 1 134,433

Sort (cost=4.46..4.46 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=134,433)

  • Sort Key: rh_2.cln_receipt_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=538,492
32. 268.866 268.866 ↑ 1.0 1 134,433

Index Scan using ndx_vstrcpthistry_ctid on cln_receipthistory rh_2 (cost=0.43..4.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=134,433)

  • Index Cond: (cln_collectiontxn_id = ct.cln_collectiontxn_id)
  • Buffers: shared hit=538,492
33. 268.866 268.866 ↑ 1.0 1 134,433

Index Scan using ndx_rcptchng_recptid on cln_receiptchanged rc_3 (cost=0.43..4.45 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=134,433)

  • Index Cond: (cln_receipt_id = rh_2.cln_receipt_id)
  • Buffers: shared hit=536,464
34. 264.824 264.824 ↑ 1.0 1 132,412

Index Only Scan using ndx_receiptchangedetail_receipt on cln_receiptchangedetail rcd_3 (cost=0.43..0.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=132,412)

  • Index Cond: (cln_receiptchanged_id = rc_3.cln_receiptchanged_id)
  • Heap Fetches: 933
  • Buffers: shared hit=399,426
35. 264.808 264.808 ↑ 1.0 1 132,404

Index Scan using ndx_rcptchng_operdate on cln_receiptchanged rc_2 (cost=0.43..4.45 rows=1 width=37) (actual time=0.002..0.002 rows=1 loops=132,404)

  • Index Cond: (operated_datetime = rc_3.operated_datetime)
  • Filter: (rc_3.cln_collectiontxn_id = cln_collectiontxn_id)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=530,401
36. 264.808 264.808 ↓ 0.0 0 132,404

Index Scan using ndx_receiptchangedetail_receipt on cln_receiptchangedetail rcd_2 (cost=0.43..0.51 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=132,404)

  • Index Cond: (cln_receiptchanged_id = rc_2.cln_receiptchanged_id)
  • Filter: (stakeholder_id IS NOT NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=530,586
37. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_receiptpayment_receipt on cln_receiptpayment rp (cost=0.43..0.49 rows=1 width=8) (never executed)

  • Index Cond: (cln_receipt_id = rc_2.cln_receipt_id)
  • Filter: ((stakeholder_type)::text = 'STY3'::text)
38. 1.866 27.990 ↑ 1.0 1 1,866

Aggregate (cost=10.36..10.37 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=1,866)

  • Buffers: shared hit=26,051
39. 0.000 26.124 ↓ 0.0 0 1,866

GroupAggregate (cost=10.31..10.34 rows=1 width=40) (actual time=0.014..0.014 rows=0 loops=1,866)

  • Group Key: rcd_4.stakeholder_id
  • Buffers: shared hit=26,051
40. 1.866 26.124 ↓ 0.0 0 1,866

Sort (cost=10.31..10.32 rows=1 width=16) (actual time=0.014..0.014 rows=0 loops=1,866)

  • Sort Key: rcd_4.stakeholder_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=26,051
41. 1.866 24.258 ↓ 0.0 0 1,866

Nested Loop Semi Join (cost=5.75..10.30 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1,866)

  • Buffers: shared hit=26,051
42. 1.052 22.392 ↓ 0.0 0 1,866

Nested Loop (cost=5.32..9.58 rows=1 width=32) (actual time=0.012..0.012 rows=0 loops=1,866)

  • Buffers: shared hit=26,051
43. 4.556 16.794 ↑ 1.0 1 1,866

Nested Loop (cost=4.89..8.93 rows=1 width=24) (actual time=0.007..0.009 rows=1 loops=1,866)

  • Buffers: shared hit=16,943
44. 0.000 7.464 ↑ 1.0 1 1,866

Unique (cost=4.46..4.46 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,866)

  • Buffers: shared hit=7,496
45. 1.866 7.464 ↑ 1.0 1 1,866

Sort (cost=4.46..4.46 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,866)

  • Sort Key: rh_3.cln_receipt_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=7,496
46. 5.598 5.598 ↑ 1.0 1 1,866

Index Scan using ndx_vstrcpthistry_ctid on cln_receipthistory rh_3 (cost=0.43..4.45 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1,866)

  • Index Cond: (cln_collectiontxn_id = ct.cln_collectiontxn_id)
  • Buffers: shared hit=7,496
47. 4.774 4.774 ↑ 1.0 1 2,387

Index Scan using ndx_rcptchng_recptid on cln_receiptchanged rc_4 (cost=0.43..4.45 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=2,387)

  • Index Cond: (cln_receipt_id = rh_3.cln_receipt_id)
  • Filter: (operated_datetime <= ct.txn_datetime)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=9,447
48. 4.546 4.546 ↓ 0.0 0 2,273

Index Scan using ndx_receiptchangedetail_receipt on cln_receiptchangedetail rcd_4 (cost=0.43..0.64 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=2,273)

  • Index Cond: (cln_receiptchanged_id = rc_4.cln_receiptchanged_id)
  • Filter: (stakeholder_id IS NOT NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=9,108
49. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_receiptpayment_receipt on cln_receiptpayment rp_1 (cost=0.43..0.57 rows=1 width=8) (never executed)

  • Index Cond: (cln_receipt_id = rc_4.cln_receipt_id)
  • Filter: ((stakeholder_type)::text = 'STY3'::text)
50. 136.299 136.299 ↑ 1.0 1 136,299

Index Scan using pk_ptf_clinic on ptf_clinic (cost=0.28..4.29 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=136,299)

  • Index Cond: (ptf_clinic_id = ct.ptf_clinic_id)
  • Buffers: shared hit=408,897
51.          

SubPlan (for Subquery Scan)

52. 0.000 272.598 ↑ 1.0 1 136,299

Aggregate (cost=4.47..4.48 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=136,299)

  • Buffers: shared hit=544,011
53. 272.598 272.598 ↑ 1.0 1 136,299

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment (cost=0.43..4.46 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=136,299)

  • Index Cond: (cln_collectiontxn_id = aa.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text <> ALL ('{PYM3,PYM4,PYM5,PYMYYDJ,PYMYHQ,PYMZSJ,PYMML,PYMZZJF,PYMZZCZJ,PYMZZZSJ}'::text[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=544,011
54. 0.020 77.588 ↓ 10.0 10 1

Subquery Scan on *SELECT* 2 (cost=2,501.70..66,407.35 rows=1 width=1,100) (actual time=17.589..77.588 rows=10 loops=1)

  • Buffers: shared hit=39,911
55. 0.119 77.568 ↓ 10.0 10 1

Nested Loop (cost=2,501.70..66,407.32 rows=1 width=820) (actual time=17.584..77.568 rows=10 loops=1)

  • Buffers: shared hit=39,911
56. 0.018 77.079 ↓ 10.0 10 1

Nested Loop (cost=2,501.27..66,380.90 rows=1 width=48) (actual time=17.492..77.079 rows=10 loops=1)

  • Buffers: shared hit=39,680
57. 0.016 77.001 ↑ 1.5 10 1

Nested Loop (cost=2,500.84..66,361.26 rows=15 width=24) (actual time=17.480..77.001 rows=10 loops=1)

  • Buffers: shared hit=39,640
58. 65.749 76.905 ↑ 32.9 10 1

Bitmap Heap Scan on cln_receipthistory rh_1 (cost=2,500.41..64,909.21 rows=329 width=16) (actual time=17.461..76.905 rows=10 loops=1)

  • Recheck Cond: (data_top_id = '2503887478'::bigint)
  • Filter: ((receipt_txn_status)::text = 'RHS5'::text)
  • Rows Removed by Filter: 136,820
  • Heap Blocks: exact=38,743
  • Buffers: shared hit=39,600
59. 11.156 11.156 ↓ 1.0 136,830 1

Bitmap Index Scan on ndx_vstrcpthistry_topid_txndt (cost=0.00..2,500.33 rows=131,720 width=0) (actual time=11.156..11.156 rows=136,830 loops=1)

  • Index Cond: (data_top_id = '2503887478'::bigint)
  • Buffers: shared hit=857
60. 0.080 0.080 ↑ 1.0 1 10

Index Scan using pk_cln_receipt on cln_receipt r_1 (cost=0.43..4.41 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=10)

  • Index Cond: (cln_receipt_id = rh_1.cln_receipt_id)
  • Filter: (ptf_clinic_id = '2503888852'::bigint)
  • Buffers: shared hit=40
61. 0.060 0.060 ↑ 1.0 1 10

Index Scan using pk_cln_patient on cln_patient ppt_1 (cost=0.43..1.31 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=10)

  • Index Cond: (cln_patient_id = r_1.cln_patient_id)
  • Filter: (ptf_topclinic_id = '2503887478'::bigint)
  • Buffers: shared hit=40
62. 0.060 0.060 ↑ 1.0 1 10

Index Scan using pk_cln_collectiontxn on cln_collectiontxn ct_1 (cost=0.43..4.43 rows=1 width=40) (actual time=0.006..0.006 rows=1 loops=10)

  • Index Cond: (cln_collectiontxn_id = rh_1.cln_collectiontxn_id)
  • Buffers: shared hit=40
63.          

SubPlan (for Nested Loop)

64. 0.040 0.040 ↑ 1.0 1 10

Index Scan using pk_cln_user on cln_user cln_user_1 (cost=0.29..4.30 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=10)

  • Index Cond: (cln_user_id = ct_1.txn_oper_id)
  • Buffers: shared hit=30
65. 0.030 0.170 ↑ 1.0 1 10

Nested Loop (cost=0.86..8.91 rows=1 width=32) (actual time=0.015..0.017 rows=1 loops=10)

  • Buffers: shared hit=90
66. 0.080 0.080 ↑ 1.0 1 10

Index Scan using ndx_rcptchng_recptid on cln_receiptchanged rc_5 (cost=0.43..4.45 rows=1 width=8) (actual time=0.006..0.008 rows=1 loops=10)

  • Index Cond: (cln_receipt_id = r_1.cln_receipt_id)
  • Filter: (((changed_type)::text = 'RCT1'::text) AND (cln_collectiontxn_id = ct_1.cln_collectiontxn_id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=50
67. 0.060 0.060 ↑ 1.0 1 10

Index Scan using ndx_receiptchangedetail_receipt on cln_receiptchangedetail rcd_5 (cost=0.43..4.45 rows=1 width=18) (actual time=0.006..0.006 rows=1 loops=10)

  • Index Cond: (cln_receiptchanged_id = rc_5.cln_receiptchanged_id)
  • Filter: (stakeholder_id IS NULL)
  • Buffers: shared hit=40
68. 0.020 0.080 ↑ 1.0 1 10

Aggregate (cost=4.47..4.48 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=10)

  • Buffers: shared hit=41
69. 0.060 0.060 ↑ 1.0 1 10

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_2 (cost=0.43..4.46 rows=1 width=5) (actual time=0.006..0.006 rows=1 loops=10)

  • Index Cond: (cln_collectiontxn_id = ct_1.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text <> ALL ('{PYM3,PYM4,PYM5,PYMYYDJ,PYMYHQ,PYMZSJ,PYMML,PYMZZJF,PYMZZCZJ,PYMZZZSJ}'::text[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=41
70. 0.020 0.020 ↑ 1.0 1 10

Index Scan using pk_ptf_clinic on ptf_clinic ptf_clinic_1 (cost=0.28..4.29 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=10)

  • Index Cond: (ptf_clinic_id = ct_1.ptf_clinic_id)
  • Buffers: shared hit=30
71. 0.363 26.209 ↓ 794.0 794 1

Subquery Scan on *SELECT* 3 (cost=16,579.55..16,597.27 rows=1 width=1,100) (actual time=19.865..26.209 rows=794 loops=1)

  • Buffers: shared hit=25,932
72. 2.758 25.846 ↓ 794.0 794 1

GroupAggregate (cost=16,579.55..16,597.24 rows=1 width=940) (actual time=19.860..25.846 rows=794 loops=1)

  • Group Key: ct_2.cln_collectiontxn_id, ((SubPlan 11)), ppt_2.cln_patient_id, ((SubPlan 14))
  • Buffers: shared hit=25,932
73. 0.593 19.929 ↓ 810.0 810 1

Sort (cost=16,579.55..16,579.55 rows=1 width=721) (actual time=19.809..19.929 rows=810 loops=1)

  • Sort Key: ct_2.cln_collectiontxn_id, ((SubPlan 11)), ppt_2.cln_patient_id, ((SubPlan 14))
  • Sort Method: quicksort Memory: 239kB
  • Buffers: shared hit=22,811
74. 1.381 19.336 ↓ 810.0 810 1

Nested Loop (cost=68.74..16,579.54 rows=1 width=721) (actual time=0.494..19.336 rows=810 loops=1)

  • Buffers: shared hit=22,811
75. 0.754 13.096 ↓ 20.4 3,239 1

Nested Loop (cost=68.31..15,901.52 rows=159 width=69) (actual time=0.461..13.096 rows=3,239 loops=1)

  • Buffers: shared hit=14,711
76. 2.342 2.625 ↑ 1.0 3,239 1

Bitmap Heap Scan on cln_packagesalelog pl (cost=67.88..3,149.48 rows=3,285 width=53) (actual time=0.447..2.625 rows=3,239 loops=1)

  • Recheck Cond: (data_top_id = '2503887478'::bigint)
  • Heap Blocks: exact=1,733
  • Buffers: shared hit=1,755
77. 0.283 0.283 ↑ 1.0 3,242 1

Bitmap Index Scan on ndx_psl_topid_canceldt (cost=0.00..67.06 rows=3,285 width=0) (actual time=0.283..0.283 rows=3,242 loops=1)

  • Index Cond: (data_top_id = '2503887478'::bigint)
  • Buffers: shared hit=22
78. 9.717 9.717 ↑ 1.0 1 3,239

Index Scan using pk_cln_patient on cln_patient ppt_2 (cost=0.43..3.88 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=3,239)

  • Index Cond: (cln_patient_id = pl.cln_patient_id)
  • Filter: (ptf_topclinic_id = '2503887478'::bigint)
  • Buffers: shared hit=12,956
79. 3.239 3.239 ↓ 0.0 0 3,239

Index Scan using pk_cln_collectiontxn on cln_collectiontxn ct_2 (cost=0.43..4.21 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=3,239)

  • Index Cond: (cln_collectiontxn_id = pl.cln_collectiontxn_id)
  • Filter: ((data_top_id = '2503887478'::bigint) AND (ptf_clinic_id = '2503888852'::bigint))
  • Buffers: shared hit=3,240
80.          

SubPlan (for Nested Loop)

81. 0.810 0.810 ↑ 1.0 1 810

Index Scan using pk_cln_user on cln_user cln_user_2 (cost=0.29..4.30 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=810)

  • Index Cond: (cln_user_id = ct_2.txn_oper_id)
  • Buffers: shared hit=2,430
82. 0.810 0.810 ↑ 1.0 1 810

Index Scan using pk_ptf_clinic on ptf_clinic ptf_clinic_2 (cost=0.28..4.29 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=810)

  • Index Cond: (ptf_clinic_id = ct_2.ptf_clinic_id)
  • Buffers: shared hit=2,430
83.          

SubPlan (for GroupAggregate)

84. 0.777 3.108 ↑ 1.0 1 777

Aggregate (cost=4.46..4.47 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=777)

  • Buffers: shared hit=3,057
85. 2.331 2.331 ↓ 0.0 0 777

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_3 (cost=0.43..4.45 rows=1 width=5) (actual time=0.003..0.003 rows=0 loops=777)

  • Index Cond: (cln_collectiontxn_id = ct_2.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text = ANY ('{PYM3,PYMZZCZJ}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3,057
86. 0.000 0.051 ↑ 1.0 1 17

Aggregate (cost=4.46..4.47 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=17)

  • Buffers: shared hit=64
87. 0.051 0.051 ↓ 0.0 0 17

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_4 (cost=0.43..4.45 rows=1 width=5) (actual time=0.003..0.003 rows=0 loops=17)

  • Index Cond: (cln_collectiontxn_id = ct_2.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text = ANY ('{PYM3,PYMZZCZJ}'::text[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=64
88. 0.000 105.783 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=5,581.48..37,693.69 rows=1 width=1,073) (actual time=105.783..105.783 rows=0 loops=1)

  • Buffers: shared hit=45,862
89. 0.001 105.783 ↓ 0.0 0 1

Nested Loop (cost=5,581.48..37,693.66 rows=1 width=821) (actual time=105.783..105.783 rows=0 loops=1)

  • Buffers: shared hit=45,862
90. 0.001 105.782 ↓ 0.0 0 1

Nested Loop (cost=5,581.05..37,675.60 rows=1 width=53) (actual time=105.782..105.782 rows=0 loops=1)

  • Buffers: shared hit=45,862
91. 78.990 105.781 ↓ 0.0 0 1

Bitmap Heap Scan on cln_collectiontxn ct_3 (cost=5,580.62..37,671.15 rows=1 width=53) (actual time=105.781..105.781 rows=0 loops=1)

  • Recheck Cond: ((ptf_clinic_id = '2503888852'::bigint) AND (data_top_id = '2503887478'::bigint))
  • Filter: (((collection_type)::text = ANY ('{CTY15,CTY16,CTY26,CTY27}'::text[])) AND ((SubPlan 19) = 0))
  • Rows Removed by Filter: 156,056
  • Heap Blocks: exact=43,814
  • Buffers: shared hit=45,862
92. 1.406 26.791 ↓ 0.0 0 1

BitmapAnd (cost=5,580.62..5,580.62 rows=7,803 width=0) (actual time=26.791..26.791 rows=0 loops=1)

  • Buffers: shared hit=2,048
93. 11.731 11.731 ↑ 1.0 156,115 1

Bitmap Index Scan on ndx_clctn_clinicid (cost=0.00..2,420.18 rows=160,234 width=0) (actual time=11.730..11.731 rows=156,115 loops=1)

  • Index Cond: (ptf_clinic_id = '2503888852'::bigint)
  • Buffers: shared hit=990
94. 13.654 13.654 ↑ 1.0 156,056 1

Bitmap Index Scan on ndx_clctn_topid_txndt (cost=0.00..3,160.18 rows=160,234 width=0) (actual time=13.654..13.654 rows=156,056 loops=1)

  • Index Cond: (data_top_id = '2503887478'::bigint)
  • Buffers: shared hit=1,058
95.          

SubPlan (for Bitmap Heap Scan)

96. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.46..2.47 rows=1 width=8) (never executed)

97. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ndx_psl_ctid on cln_packagesalelog (cost=0.42..2.46 rows=2 width=0) (never executed)

  • Index Cond: (cln_collectiontxn_id = ct_3.cln_collectiontxn_id)
  • Heap Fetches: 0
98. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_patientaccount on cln_patientaccount cpa (cost=0.43..4.45 rows=1 width=16) (never executed)

  • Index Cond: (cln_patientaccount_id = ct_3.cln_patientaccount_id)
99. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_patient on cln_patient ppt_3 (cost=0.43..0.52 rows=1 width=32) (never executed)

  • Index Cond: (cln_patient_id = cpa.cln_patient_id)
  • Filter: (ptf_topclinic_id = '2503887478'::bigint)
100.          

SubPlan (for Nested Loop)

101. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_user on cln_user cln_user_3 (cost=0.29..4.30 rows=1 width=10) (never executed)

  • Index Cond: (cln_user_id = ct_3.txn_oper_id)
102. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.46..4.47 rows=1 width=32) (never executed)

103. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_5 (cost=0.43..4.45 rows=1 width=5) (never executed)

  • Index Cond: (cln_collectiontxn_id = ct_3.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text <> ALL ('{PYM4,PYMZSJ,PYMYHQ,PYMZZJF,PYMZZZSJ}'::text[]))
104. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.46..4.47 rows=1 width=32) (never executed)

105. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_6 (cost=0.43..4.46 rows=1 width=5) (never executed)

  • Index Cond: (cln_collectiontxn_id = ct_3.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text <> ALL ('{PYM3,PYM4,PYMZSJ,PYMYHQ,PYMZZJF,PYMZZCZJ,PYMZZZSJ}'::text[]))
106. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_ptf_clinic on ptf_clinic ptf_clinic_3 (cost=0.28..4.29 rows=1 width=28) (never executed)

  • Index Cond: (ptf_clinic_id = ct_3.ptf_clinic_id)
107. 6.525 306.586 ↓ 12,100.0 12,100 1

Subquery Scan on *SELECT* 5 (cost=5,582.26..18,959.83 rows=1 width=1,073) (actual time=30.964..306.586 rows=12,100 loops=1)

  • Buffers: shared hit=348,620
108. 21.258 300.061 ↓ 12,100.0 12,100 1

Nested Loop (cost=5,582.26..18,959.79 rows=1 width=793) (actual time=30.959..300.061 rows=12,100 loops=1)

  • Buffers: shared hit=348,620
109. 9.239 194.103 ↓ 6,050.0 12,100 1

Nested Loop (cost=5,581.83..18,945.75 rows=2 width=53) (actual time=30.907..194.103 rows=12,100 loops=1)

  • Buffers: shared hit=179,138
110. 0.000 160.664 ↓ 6,050.0 12,100 1

Nested Loop (cost=5,581.40..18,941.12 rows=2 width=53) (actual time=30.897..160.664 rows=12,100 loops=1)

  • Buffers: shared hit=130,731
111. 11.505 137.818 ↓ 100.1 12,115 1

Nested Loop (cost=5,581.11..18,902.60 rows=121 width=48) (actual time=30.886..137.818 rows=12,115 loops=1)

  • Buffers: shared hit=94,341
112. 77.578 102.113 ↓ 99.2 12,100 1

Bitmap Heap Scan on cln_collectiontxn ct_4 (cost=5,580.68..18,358.79 rows=122 width=40) (actual time=30.872..102.113 rows=12,100 loops=1)

  • Recheck Cond: ((ptf_clinic_id = '2503888852'::bigint) AND (data_top_id = '2503887478'::bigint))
  • Filter: ((collection_type)::text = ANY ('{CTY13,CTY14}'::text[]))
  • Rows Removed by Filter: 143,956
  • Heap Blocks: exact=43,814
  • Buffers: shared hit=45,862
113. 1.392 24.535 ↓ 0.0 0 1

BitmapAnd (cost=5,580.68..5,580.68 rows=7,803 width=0) (actual time=24.535..24.535 rows=0 loops=1)

  • Buffers: shared hit=2,048
114. 10.316 10.316 ↑ 1.0 156,115 1

Bitmap Index Scan on ndx_clctn_clinicid (cost=0.00..2,420.18 rows=160,234 width=0) (actual time=10.316..10.316 rows=156,115 loops=1)

  • Index Cond: (ptf_clinic_id = '2503888852'::bigint)
  • Buffers: shared hit=990
115. 12.827 12.827 ↑ 1.0 156,056 1

Bitmap Index Scan on ndx_clctn_topid_txndt (cost=0.00..3,160.18 rows=160,234 width=0) (actual time=12.827..12.827 rows=156,056 loops=1)

  • Index Cond: (data_top_id = '2503887478'::bigint)
  • Buffers: shared hit=1,058
116. 24.200 24.200 ↑ 1.0 1 12,100

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cp (cost=0.43..4.45 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=12,100)

  • Index Cond: (cln_collectiontxn_id = ct_4.cln_collectiontxn_id)
  • Buffers: shared hit=48,479
117. 24.230 24.230 ↑ 1.0 1 12,115

Index Scan using ndx_bookingdeposit_collectionpa on cln_bookingdeposit bd (cost=0.29..0.31 rows=1 width=21) (actual time=0.001..0.002 rows=1 loops=12,115)

  • Index Cond: (cln_collectionpayment_id = cp.cln_collectionpayment_id)
  • Buffers: shared hit=36,390
118. 24.200 24.200 ↑ 1.0 1 12,100

Index Scan using pk_cln_visit on cln_visit v (cost=0.43..2.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=12,100)

  • Index Cond: (cln_visit_id = bd.cln_visit_id)
  • Buffers: shared hit=48,407
119. 36.300 36.300 ↑ 1.0 1 12,100

Index Scan using pk_cln_patient on cln_patient ppt_4 (cost=0.43..0.48 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=12,100)

  • Index Cond: (cln_patient_id = v.cln_patient_id)
  • Filter: (ptf_topclinic_id = '2503887478'::bigint)
  • Buffers: shared hit=48,403
120.          

SubPlan (for Nested Loop)

121. 12.100 12.100 ↑ 1.0 1 12,100

Index Scan using pk_cln_user on cln_user cln_user_4 (cost=0.29..4.30 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=12,100)

  • Index Cond: (cln_user_id = ct_4.txn_oper_id)
  • Buffers: shared hit=36,300
122. 12.100 24.200 ↑ 1.0 1 12,100

Aggregate (cost=4.46..4.47 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=12,100)

  • Buffers: shared hit=48,479
123. 12.100 12.100 ↓ 0.0 0 12,100

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_7 (cost=0.43..4.46 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=12,100)

  • Index Cond: (cln_collectiontxn_id = ct_4.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text = ANY ('{PYM3,PYMZSJ,PYM4,PYMYHQ,PYMZZJF,PYMZZCZJ,PYMZZZSJ}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=48,479
124. 12.100 12.100 ↑ 1.0 1 12,100

Index Scan using pk_ptf_clinic on ptf_clinic ptf_clinic_4 (cost=0.28..4.29 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=12,100)

  • Index Cond: (ptf_clinic_id = ct_4.ptf_clinic_id)
  • Buffers: shared hit=36,300
125. 2.989 206.127 ↓ 5,451.0 5,451 1

Subquery Scan on *SELECT* 6 (cost=5,582.42..19,346.25 rows=1 width=1,073) (actual time=30.963..206.127 rows=5,451 loops=1)

  • Buffers: shared hit=187,698
126. 11.516 203.138 ↓ 5,451.0 5,451 1

Nested Loop (cost=5,582.42..19,346.21 rows=1 width=793) (actual time=30.958..203.138 rows=5,451 loops=1)

  • Buffers: shared hit=187,698
127. 0.919 153.465 ↓ 218.0 5,451 1

Nested Loop (cost=5,582.00..19,320.13 rows=25 width=53) (actual time=30.913..153.465 rows=5,451 loops=1)

  • Buffers: shared hit=111,352
128. 3.838 136.193 ↓ 218.0 5,451 1

Nested Loop (cost=5,581.57..19,304.61 rows=25 width=53) (actual time=30.902..136.193 rows=5,451 loops=1)

  • Buffers: shared hit=89,545
129. 1.928 121.453 ↓ 28.5 5,451 1

Nested Loop (cost=5,581.15..19,217.12 rows=191 width=48) (actual time=30.887..121.453 rows=5,451 loops=1)

  • Buffers: shared hit=67,692
130. 78.568 103.172 ↓ 28.2 5,451 1

Bitmap Heap Scan on cln_collectiontxn ct_5 (cost=5,580.72..18,358.82 rows=193 width=40) (actual time=30.874..103.172 rows=5,451 loops=1)

  • Recheck Cond: ((ptf_clinic_id = '2503888852'::bigint) AND (data_top_id = '2503887478'::bigint))
  • Filter: ((collection_type)::text = ANY ('{CTY7,CTY8}'::text[]))
  • Rows Removed by Filter: 150,605
  • Heap Blocks: exact=43,814
  • Buffers: shared hit=45,862
131. 1.386 24.604 ↓ 0.0 0 1

BitmapAnd (cost=5,580.72..5,580.72 rows=7,803 width=0) (actual time=24.604..24.604 rows=0 loops=1)

  • Buffers: shared hit=2,048
132. 10.351 10.351 ↑ 1.0 156,115 1

Bitmap Index Scan on ndx_clctn_clinicid (cost=0.00..2,420.18 rows=160,234 width=0) (actual time=10.351..10.351 rows=156,115 loops=1)

  • Index Cond: (ptf_clinic_id = '2503888852'::bigint)
  • Buffers: shared hit=990
133. 12.867 12.867 ↑ 1.0 156,056 1

Bitmap Index Scan on ndx_clctn_topid_txndt (cost=0.00..3,160.18 rows=160,234 width=0) (actual time=12.867..12.867 rows=156,056 loops=1)

  • Index Cond: (data_top_id = '2503887478'::bigint)
  • Buffers: shared hit=1,058
134. 16.353 16.353 ↑ 1.0 1 5,451

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cp_1 (cost=0.43..4.44 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=5,451)

  • Index Cond: (cln_collectiontxn_id = ct_5.cln_collectiontxn_id)
  • Buffers: shared hit=21,830
135. 10.902 10.902 ↑ 1.0 1 5,451

Index Scan using ndx_pdt_cpid on cln_patientdeposittxn pd (cost=0.42..0.45 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=5,451)

  • Index Cond: (cln_collectionpayment_id = cp_1.cln_collectionpayment_id)
  • Buffers: shared hit=21,853
136. 16.353 16.353 ↑ 1.0 1 5,451

Index Scan using pk_cln_patientaccount on cln_patientaccount pa (cost=0.43..0.62 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=5,451)

  • Index Cond: (cln_patientaccount_id = pd.cln_patientaccount_id)
  • Buffers: shared hit=21,807
137. 16.353 16.353 ↑ 1.0 1 5,451

Index Scan using pk_cln_patient on cln_patient ppt_5 (cost=0.43..0.52 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=5,451)

  • Index Cond: (cln_patient_id = pa.cln_patient_id)
  • Filter: (ptf_topclinic_id = '2503887478'::bigint)
  • Buffers: shared hit=21,810
138.          

SubPlan (for Nested Loop)

139. 5.451 5.451 ↑ 1.0 1 5,451

Index Scan using pk_cln_user on cln_user cln_user_5 (cost=0.29..4.30 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=5,451)

  • Index Cond: (cln_user_id = ct_5.txn_oper_id)
  • Buffers: shared hit=16,353
140. 0.000 10.902 ↑ 1.0 1 5,451

Aggregate (cost=4.46..4.47 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=5,451)

  • Buffers: shared hit=21,830
141. 10.902 10.902 ↓ 0.0 0 5,451

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_8 (cost=0.43..4.46 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=5,451)

  • Index Cond: (cln_collectiontxn_id = ct_5.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text = ANY ('{PYM3,PYMZSJ,PYM4,PYMYHQ,PYMZZJF,PYMZZCZJ,PYMZZZSJ}'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=21,830
142. 5.451 5.451 ↑ 1.0 1 5,451

Index Scan using pk_ptf_clinic on ptf_clinic ptf_clinic_5 (cost=0.28..4.29 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=5,451)

  • Index Cond: (ptf_clinic_id = ct_5.ptf_clinic_id)
  • Buffers: shared hit=16,353
143. 0.747 143.642 ↓ 701.0 1,402 1

Subquery Scan on *SELECT* 7 (cost=6,454.57..19,387.87 rows=2 width=1,071) (actual time=39.503..143.642 rows=1,402 loops=1)

  • Buffers: shared hit=68,765
144. 5.480 142.895 ↓ 701.0 1,402 1

Nested Loop (cost=6,454.57..19,387.80 rows=2 width=819) (actual time=39.498..142.895 rows=1,402 loops=1)

  • Buffers: shared hit=68,765
145. 24.331 126.199 ↓ 34.2 1,402 1

Hash Join (cost=6,454.14..19,242.41 rows=41 width=54) (actual time=39.435..126.199 rows=1,402 loops=1)

  • Hash Cond: (ct_6.cln_collectiontxn_id = oc.cln_collectiontxn_id)
  • Buffers: shared hit=46,323
146. 68.885 94.019 ↓ 20.0 156,056 1

Bitmap Heap Scan on cln_collectiontxn ct_6 (cost=5,584.52..18,343.12 rows=7,803 width=40) (actual time=31.419..94.019 rows=156,056 loops=1)

  • Recheck Cond: ((ptf_clinic_id = '2503888852'::bigint) AND (data_top_id = '2503887478'::bigint))
  • Heap Blocks: exact=43,814
  • Buffers: shared hit=45,862
147. 1.399 25.134 ↓ 0.0 0 1

BitmapAnd (cost=5,584.52..5,584.52 rows=7,803 width=0) (actual time=25.134..25.134 rows=0 loops=1)

  • Buffers: shared hit=2,048
148. 10.824 10.824 ↑ 1.0 156,115 1

Bitmap Index Scan on ndx_clctn_clinicid (cost=0.00..2,420.18 rows=160,234 width=0) (actual time=10.824..10.824 rows=156,115 loops=1)

  • Index Cond: (ptf_clinic_id = '2503888852'::bigint)
  • Buffers: shared hit=990
149. 12.911 12.911 ↑ 1.0 156,056 1

Bitmap Index Scan on ndx_clctn_topid_txndt (cost=0.00..3,160.18 rows=160,234 width=0) (actual time=12.911..12.911 rows=156,056 loops=1)

  • Index Cond: (data_top_id = '2503887478'::bigint)
  • Buffers: shared hit=1,058
150. 2.823 7.849 ↑ 1.0 17,445 1

Hash (cost=642.61..642.61 rows=18,161 width=22) (actual time=7.849..7.849 rows=17,445 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,212kB
  • Buffers: shared hit=461
151. 5.026 5.026 ↓ 1.0 18,164 1

Seq Scan on cln_ordercharge oc (cost=0.00..642.61 rows=18,161 width=22) (actual time=0.008..5.026 rows=18,164 loops=1)

  • Buffers: shared hit=461
152. 4.206 4.206 ↑ 1.0 1 1,402

Index Scan using pk_cln_patient on cln_patient ppt_6 (cost=0.43..2.69 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1,402)

  • Index Cond: (cln_patient_id = oc.cln_patient_id)
  • Filter: (ptf_topclinic_id = '2503887478'::bigint)
  • Buffers: shared hit=5,608
153.          

SubPlan (for Nested Loop)

154. 1.402 1.402 ↑ 1.0 1 1,402

Index Scan using pk_cln_user on cln_user cln_user_6 (cost=0.29..4.30 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=1,402)

  • Index Cond: (cln_user_id = ct_6.txn_oper_id)
  • Buffers: shared hit=4,206
155. 0.000 2.804 ↑ 1.0 1 1,402

Aggregate (cost=4.46..4.47 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1,402)

  • Buffers: shared hit=4,211
156. 2.804 2.804 ↓ 0.0 0 1,402

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_9 (cost=0.43..4.45 rows=1 width=5) (actual time=0.002..0.002 rows=0 loops=1,402)

  • Index Cond: (cln_collectiontxn_id = ct_6.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text = ANY ('{PYMZSJ,PYM4,PYMZZJF,PYMZZZSJ}'::text[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4,211
157. 0.000 1.402 ↑ 1.0 1 1,402

Aggregate (cost=4.46..4.47 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1,402)

  • Buffers: shared hit=4,211
158. 1.402 1.402 ↓ 0.0 0 1,402

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_10 (cost=0.43..4.46 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=1,402)

  • Index Cond: (cln_collectiontxn_id = ct_6.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text = ANY ('{PYM3,PYMZSJ,PYM4,PYMZZJF,PYMZZCZJ,PYMZZZSJ}'::text[]))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=4,211
159. 1.402 1.402 ↑ 1.0 1 1,402

Index Scan using pk_ptf_clinic on ptf_clinic ptf_clinic_6 (cost=0.28..4.29 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=1,402)

  • Index Cond: (ptf_clinic_id = ct_6.ptf_clinic_id)
  • Buffers: shared hit=4,206
160. 0.001 98.939 ↓ 0.0 0 1

Subquery Scan on *SELECT* 8 (cost=5,582.34..18,402.70 rows=1 width=1,046) (actual time=98.939..98.939 rows=0 loops=1)

  • Buffers: shared hit=45,862
161. 0.001 98.938 ↓ 0.0 0 1

Nested Loop (cost=5,582.34..18,402.66 rows=1 width=766) (actual time=98.937..98.938 rows=0 loops=1)

  • Buffers: shared hit=45,862
162. 0.001 98.937 ↓ 0.0 0 1

Nested Loop (cost=5,581.91..18,393.58 rows=1 width=53) (actual time=98.936..98.937 rows=0 loops=1)

  • Buffers: shared hit=45,862
163. 0.001 98.936 ↓ 0.0 0 1

Nested Loop (cost=5,581.48..18,393.06 rows=1 width=53) (actual time=98.936..98.936 rows=0 loops=1)

  • Buffers: shared hit=45,862
164. 0.001 98.935 ↓ 0.0 0 1

Nested Loop (cost=5,581.05..18,389.93 rows=6 width=61) (actual time=98.935..98.935 rows=0 loops=1)

  • Buffers: shared hit=45,862
165. 74.199 98.934 ↓ 0.0 0 1

Bitmap Heap Scan on cln_collectiontxn ct_7 (cost=5,580.62..18,358.73 rows=7 width=40) (actual time=98.934..98.934 rows=0 loops=1)

  • Recheck Cond: ((ptf_clinic_id = '2503888852'::bigint) AND (data_top_id = '2503887478'::bigint))
  • Filter: ((collection_type)::text = ANY ('{CTY20,CTY21}'::text[]))
  • Rows Removed by Filter: 156,056
  • Heap Blocks: exact=43,814
  • Buffers: shared hit=45,862
166. 1.377 24.735 ↓ 0.0 0 1

BitmapAnd (cost=5,580.62..5,580.62 rows=7,803 width=0) (actual time=24.735..24.735 rows=0 loops=1)

  • Buffers: shared hit=2,048
167. 10.514 10.514 ↑ 1.0 156,115 1

Bitmap Index Scan on ndx_clctn_clinicid (cost=0.00..2,420.18 rows=160,234 width=0) (actual time=10.514..10.514 rows=156,115 loops=1)

  • Index Cond: (ptf_clinic_id = '2503888852'::bigint)
  • Buffers: shared hit=990
168. 12.844 12.844 ↑ 1.0 156,056 1

Bitmap Index Scan on ndx_clctn_topid_txndt (cost=0.00..3,160.18 rows=160,234 width=0) (actual time=12.844..12.844 rows=156,056 loops=1)

  • Index Cond: (data_top_id = '2503887478'::bigint)
  • Buffers: shared hit=1,058
169. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_rcptchng_ctid on cln_receiptchanged rc (cost=0.43..4.45 rows=1 width=29) (never executed)

  • Index Cond: (cln_collectiontxn_id = ct_7.cln_collectiontxn_id)
170. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_receiptchangedetail_receipt on cln_receiptchangedetail rcd (cost=0.43..0.51 rows=1 width=8) (never executed)

  • Index Cond: (cln_receiptchanged_id = rc.cln_receiptchanged_id)
  • Filter: (stakeholder_id IS NOT NULL)
171. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_receipt on cln_receipt r_2 (cost=0.43..0.52 rows=1 width=16) (never executed)

  • Index Cond: (cln_receipt_id = rc.cln_receipt_id)
172. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_patient on cln_patient ppt_7 (cost=0.43..0.49 rows=1 width=32) (never executed)

  • Index Cond: (cln_patient_id = r_2.cln_patient_id)
  • Filter: (ptf_topclinic_id = '2503887478'::bigint)
173.          

SubPlan (for Nested Loop)

174. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_user on cln_user cln_user_7 (cost=0.29..4.30 rows=1 width=10) (never executed)

  • Index Cond: (cln_user_id = ct_7.txn_oper_id)
175. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_ptf_clinic on ptf_clinic ptf_clinic_7 (cost=0.28..4.29 rows=1 width=28) (never executed)

  • Index Cond: (ptf_clinic_id = ct_7.ptf_clinic_id)
176. 0.001 246.489 ↓ 0.0 0 1

Subquery Scan on *SELECT* 9 (cost=5,585.98..33,517.53 rows=23 width=1,073) (actual time=246.488..246.489 rows=0 loops=1)

  • Buffers: shared hit=326,459
177. 0.001 246.488 ↓ 0.0 0 1

Nested Loop (cost=5,585.98..33,516.73 rows=23 width=793) (actual time=246.487..246.488 rows=0 loops=1)

  • Buffers: shared hit=326,459
178. 0.001 246.487 ↓ 0.0 0 1

Nested Loop (cost=5,585.55..33,205.20 rows=23 width=85) (actual time=246.487..246.487 rows=0 loops=1)

  • Buffers: shared hit=326,459
179. 0.001 246.486 ↓ 0.0 0 1

Nested Loop (cost=5,585.12..32,977.00 rows=469 width=61) (actual time=246.486..246.486 rows=0 loops=1)

  • Buffers: shared hit=326,459
180. 0.000 246.485 ↓ 0.0 0 1

Nested Loop (cost=5,584.69..32,734.85 rows=469 width=61) (actual time=246.485..246.485 rows=0 loops=1)

  • Buffers: shared hit=326,459
181. 43.830 246.485 ↓ 0.0 0 1

Nested Loop Anti Join (cost=5,584.26..30,630.33 rows=532 width=45) (actual time=246.484..246.485 rows=0 loops=1)

  • Buffers: shared hit=326,459
182. 85.085 109.726 ↓ 14.5 92,929 1

Bitmap Heap Scan on cln_collectiontxn ct_8 (cost=5,583.83..18,361.94 rows=6,419 width=45) (actual time=30.940..109.726 rows=92,929 loops=1)

  • Recheck Cond: ((ptf_clinic_id = '2503888852'::bigint) AND (data_top_id = '2503887478'::bigint))
  • Filter: ((collection_type)::text = ANY ('{CTY1,CTY2}'::text[]))
  • Rows Removed by Filter: 63,127
  • Heap Blocks: exact=43,814
  • Buffers: shared hit=45,862
183. 1.398 24.641 ↓ 0.0 0 1

BitmapAnd (cost=5,583.83..5,583.83 rows=7,803 width=0) (actual time=24.641..24.641 rows=0 loops=1)

  • Buffers: shared hit=2,048
184. 10.356 10.356 ↑ 1.0 156,115 1

Bitmap Index Scan on ndx_clctn_clinicid (cost=0.00..2,420.18 rows=160,234 width=0) (actual time=10.356..10.356 rows=156,115 loops=1)

  • Index Cond: (ptf_clinic_id = '2503888852'::bigint)
  • Buffers: shared hit=990
185. 12.887 12.887 ↑ 1.0 156,056 1

Bitmap Index Scan on ndx_clctn_topid_txndt (cost=0.00..3,160.18 rows=160,234 width=0) (actual time=12.887..12.887 rows=156,056 loops=1)

  • Index Cond: (data_top_id = '2503887478'::bigint)
  • Buffers: shared hit=1,058
186. 92.929 92.929 ↑ 1.0 1 92,929

Index Only Scan using ndx_vstrcpthistry_ctid on cln_receipthistory (cost=0.43..1.91 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=92,929)

  • Index Cond: (cln_collectiontxn_id = ct_8.cln_collectiontxn_id)
  • Heap Fetches: 486
  • Buffers: shared hit=280,597
187. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_rcptchng_ctid on cln_receiptchanged rc_1 (cost=0.43..3.95 rows=1 width=24) (never executed)

  • Index Cond: (cln_collectiontxn_id = ct_8.cln_collectiontxn_id)
188. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_receipt on cln_receipt r_3 (cost=0.43..0.52 rows=1 width=16) (never executed)

  • Index Cond: (cln_receipt_id = rc_1.cln_receipt_id)
189. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_patient on cln_patient ppt_8 (cost=0.43..0.49 rows=1 width=32) (never executed)

  • Index Cond: (cln_patient_id = r_3.cln_patient_id)
  • Filter: (ptf_topclinic_id = '2503887478'::bigint)
190. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ndx_receiptchangedetail_receipt on cln_receiptchangedetail rcd_1 (cost=0.43..0.46 rows=1 width=8) (never executed)

  • Index Cond: (cln_receiptchanged_id = rc_1.cln_receiptchanged_id)
  • Heap Fetches: 0
191.          

SubPlan (for Nested Loop)

192. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_user on cln_user cln_user_8 (cost=0.29..4.30 rows=1 width=10) (never executed)

  • Index Cond: (cln_user_id = ct_8.txn_oper_id)
193. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.46..4.47 rows=1 width=32) (never executed)

194. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_11 (cost=0.43..4.46 rows=1 width=5) (never executed)

  • Index Cond: (cln_collectiontxn_id = ct_8.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text = ANY ('{PYM3,PYM5,PYM4,PYMYHQ,PYMZSJ,PYMZZJF,PYMZZCZJ,PYMZZZSJ}'::text[]))
195. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_ptf_clinic on ptf_clinic ptf_clinic_8 (cost=0.28..4.29 rows=1 width=28) (never executed)

  • Index Cond: (ptf_clinic_id = ct_8.ptf_clinic_id)
196. 0.001 0.548 ↓ 0.0 0 1

Subquery Scan on *SELECT* 10 (cost=6.33..857.85 rows=1 width=1,100) (actual time=0.548..0.548 rows=0 loops=1)

  • Buffers: shared hit=446
197. 0.001 0.547 ↓ 0.0 0 1

Nested Loop (cost=6.33..857.83 rows=1 width=904) (actual time=0.547..0.547 rows=0 loops=1)

  • Buffers: shared hit=446
198. 0.000 0.546 ↓ 0.0 0 1

Nested Loop (cost=5.91..844.21 rows=1 width=65) (actual time=0.546..0.546 rows=0 loops=1)

  • Buffers: shared hit=446
199. 0.000 0.546 ↓ 0.0 0 1

Nested Loop (cost=5.48..839.77 rows=1 width=65) (actual time=0.546..0.546 rows=0 loops=1)

  • Buffers: shared hit=446
200. 0.124 0.226 ↑ 1.1 107 1

Hash Join (cost=5.05..31.09 rows=117 width=20) (actual time=0.174..0.226 rows=107 loops=1)

  • Hash Cond: (gd.cln_patientgroupbuying_id = gp.cln_patientgroupbuying_id)
  • Buffers: shared hit=17
201. 0.062 0.062 ↑ 1.0 791 1

Seq Scan on cln_groupbuyingdeposit gd (cost=0.00..21.91 rows=791 width=16) (actual time=0.006..0.062 rows=791 loops=1)

  • Buffers: shared hit=14
202. 0.016 0.040 ↑ 1.0 91 1

Hash (cost=3.91..3.91 rows=91 width=20) (actual time=0.040..0.040 rows=91 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=3
203. 0.024 0.024 ↑ 1.0 91 1

Seq Scan on cln_groupbuyingproduct gp (cost=0.00..3.91 rows=91 width=20) (actual time=0.004..0.024 rows=91 loops=1)

  • Buffers: shared hit=3
204. 0.321 0.321 ↓ 0.0 0 107

Index Scan using pk_cln_collectiontxn on cln_collectiontxn ct_9 (cost=0.43..6.86 rows=1 width=53) (actual time=0.003..0.003 rows=0 loops=107)

  • Index Cond: (cln_collectiontxn_id = gd.cln_collectiontxn_id)
  • Filter: (((collection_type)::text = ANY ('{CTY29,CTY30}'::text[])) AND (data_top_id = '2503887478'::bigint) AND (ptf_clinic_id = '2503888852'::bigint) AND ((SubPlan 38) = 0))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=429
205.          

SubPlan (for Index Scan)

206. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.45..2.46 rows=1 width=8) (never executed)

207. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ndx_vstrcpthistry_ctid on cln_receipthistory rh_4 (cost=0.43..2.45 rows=1 width=0) (never executed)

  • Index Cond: (cln_collectiontxn_id = ct_9.cln_collectiontxn_id)
  • Heap Fetches: 0
208. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_patientaccount on cln_patientaccount pa_1 (cost=0.43..4.45 rows=1 width=16) (never executed)

  • Index Cond: (cln_patientaccount_id = ct_9.cln_patientaccount_id)
209. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_patient on cln_patient ppt_9 (cost=0.43..0.52 rows=1 width=32) (never executed)

  • Index Cond: (cln_patient_id = pa_1.cln_patient_id)
210.          

SubPlan (for Nested Loop)

211. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cln_user on cln_user cln_user_9 (cost=0.29..4.30 rows=1 width=10) (never executed)

  • Index Cond: (cln_user_id = ct_9.txn_oper_id)
212. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=4.46..4.47 rows=1 width=32) (never executed)

213. 0.000 0.000 ↓ 0.0 0

Index Scan using ndx_clctnpaymnt_clctnid on cln_collectionpayment cln_collectionpayment_12 (cost=0.43..4.46 rows=1 width=5) (never executed)

  • Index Cond: (cln_collectiontxn_id = ct_9.cln_collectiontxn_id)
  • Filter: ((collection_mode)::text = ANY ('{PYM3,PYM5,PYM4,PYMYHQ,PYMZSJ,PYMZZJF,PYMZZCZJ,PYMZZZSJ}'::text[]))
214. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_ptf_clinic on ptf_clinic ptf_clinic_9 (cost=0.28..4.29 rows=1 width=28) (never executed)

  • Index Cond: (ptf_clinic_id = ct_9.ptf_clinic_id)