explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rPAF

Settings
# exclusive inclusive rows x rows loops node
1. 0.088 1,614.685 ↓ 1.5 6 1

Subquery Scan on itemview (cost=22,392.72..268,524.46 rows=4 width=24) (actual time=591.768..1,614.685 rows=6 loops=1)

  • Filter: ((itemview.tenantguid = '0ddb2280-67c8-11e8-b42f-af0946e745be'::bpchar) OR (itemview.itemscheduletypeid = 3) OR ((itemview.tenantguid IS NULL) AND (itemview.itemscheduletypeid <> 3) AND (NOT (hashed SubPlan 1))))
2. 0.005 1,614.268 ↑ 56.8 6 1

Append (cost=268.59..246,393.52 rows=341 width=1,054) (actual time=591.375..1,614.268 rows=6 loops=1)

3. 0.020 1,210.004 ↑ 83.0 4 1

Result (cost=268.59..183,284.61 rows=332 width=1,052) (actual time=591.375..1,210.004 rows=4 loops=1)

4. 0.001 1,209.984 ↑ 83.0 4 1

Append (cost=268.59..183,284.61 rows=332 width=1,052) (actual time=591.370..1,209.984 rows=4 loops=1)

5. 1.711 814.887 ↑ 54.5 4 1

Nested Loop Left Join (cost=268.59..88,196.15 rows=218 width=1,121) (actual time=591.369..814.887 rows=4 loops=1)

6. 0.004 811.296 ↑ 54.5 4 1

Append (cost=268.04..85,768.72 rows=218 width=958) (actual time=590.890..811.296 rows=4 loops=1)

7. 0.722 409.170 ↓ 0.0 0 1

Nested Loop (cost=268.04..534.93 rows=1 width=957) (actual time=409.170..409.170 rows=0 loops=1)

8. 0.371 38.598 ↓ 130.0 130 1

Nested Loop (cost=223.20..486.04 rows=1 width=463) (actual time=9.864..38.598 rows=130 loops=1)

  • Join Filter: (itemschedule.itemscheduleguid = item_1.itemscheduleguid)
9. 0.040 0.670 ↓ 39.0 39 1

Nested Loop (cost=0.28..206.91 rows=1 width=90) (actual time=0.068..0.670 rows=39 loops=1)

10. 0.435 0.435 ↓ 2.8 39 1

Seq Scan on itemschedule (cost=0.00..106.50 rows=14 width=53) (actual time=0.060..0.435 rows=39 loops=1)

  • Filter: ((recordstatusid = 1) AND (itemscheduletypeid <> ALL ('{3,4,5}'::integer[])))
  • Rows Removed by Filter: 2084
11. 0.195 0.195 ↑ 1.0 1 39

Index Scan using pk_itemschedule on itemschedule relateditemschedule (cost=0.28..7.16 rows=1 width=37) (actual time=0.005..0.005 rows=1 loops=39)

  • Index Cond: (itemscheduleguid = itemschedule.itemscheduleguid)
  • Filter: (itemscheduletypeid = ANY ('{1,2,6,7}'::integer[]))
12. 0.156 37.557 ↑ 4.7 3 39

Bitmap Heap Scan on item item_1 (cost=222.92..278.96 rows=14 width=373) (actual time=0.961..0.963 rows=3 loops=39)

  • Recheck Cond: ((tenantguid IS NOT NULL) AND (recordstatusid = 1) AND (itemscheduleguid = relateditemschedule.itemscheduleguid) AND (itemscheduleguid = ANY ('{960f9ca3-8a56-4cfe-a5b2-033f63f942ef,dbcc4448-02 (...)
  • Heap Blocks: exact=72
13. 2.769 37.401 ↓ 0.0 0 39

BitmapAnd (cost=222.92..222.92 rows=14 width=0) (actual time=0.959..0.959 rows=0 loops=39)

14. 6.591 6.591 ↑ 1.3 2,118 39

Bitmap Index Scan on idx_tenant_recordstatus_nopatient (cost=0.00..72.72 rows=2,826 width=0) (actual time=0.169..0.169 rows=2,118 loops=39)

  • Index Cond: ((tenantguid IS NOT NULL) AND (recordstatusid = 1))
15. 28.041 28.041 ↓ 3.7 10,644 39

Bitmap Index Scan on idx_item_itemscheduleguid (cost=0.00..149.46 rows=2,885 width=0) (actual time=0.719..0.719 rows=10,644 loops=39)

  • Index Cond: ((itemscheduleguid = relateditemschedule.itemscheduleguid) AND (itemscheduleguid = ANY ('{960f9ca3-8a56-4cfe-a5b2-033f63f942ef,dbcc4448-02cc-4c81-b56d-742a89ef59d7,20d287fa-055a-47da (...)
16. 1.170 369.850 ↓ 0.0 0 130

Bitmap Heap Scan on item relatedmbsitem (cost=44.84..48.88 rows=1 width=612) (actual time=2.845..2.845 rows=0 loops=130)

  • Recheck Cond: (((itemcode)::text = (item_1.itemcode)::text) AND (recordstatusid = 1) AND (tenantguid IS NULL) AND (itemscheduleguid = item_1.itemscheduleguid))
  • Filter: ((item_1.noofpatients = noofpatients) AND (lower((itemcode)::text) = '3'::text) AND ((fee_start_date IS NULL) OR ((fee_start_date <= '2019-05-14 03:05:15.028'::timestamp without time zone) AND (item_end_d (...)
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=1096
17. 1.690 368.680 ↓ 0.0 0 130

BitmapAnd (cost=44.84..44.84 rows=1 width=0) (actual time=2.836..2.836 rows=0 loops=130)

18. 2.210 2.210 ↑ 1.0 86 130

Bitmap Index Scan on idx_itemcode_recordstatus_tenant (cost=0.00..4.30 rows=88 width=0) (actual time=0.017..0.017 rows=86 loops=130)

  • Index Cond: (((itemcode)::text = (item_1.itemcode)::text) AND (recordstatusid = 1) AND (tenantguid IS NULL))
19. 364.780 364.780 ↓ 10.4 43,475 130

Bitmap Index Scan on idx_item_itemscheduleguid (cost=0.00..40.29 rows=4,189 width=0) (actual time=2.806..2.806 rows=43,475 loops=130)

  • Index Cond: (itemscheduleguid = item_1.itemscheduleguid)
20. 0.020 402.122 ↑ 54.2 4 1

Nested Loop (cost=0.28..85,231.61 rows=217 width=920) (actual time=181.719..402.122 rows=4 loops=1)

21. 402.050 402.050 ↑ 159.0 4 1

Seq Scan on item item_2 (cost=0.00..84,672.86 rows=636 width=904) (actual time=181.701..402.050 rows=4 loops=1)

  • Filter: ((tenantguid IS NULL) AND (recordstatusid = 1) AND (lower((itemcode)::text) = '3'::text) AND ((fee_start_date IS NULL) OR ((fee_start_date <= '2019-05-14 03:05:15.028'::timestamp without time zone) AND (i (...)
  • Rows Removed by Filter: 569735
22. 0.052 0.052 ↑ 1.0 1 4

Index Scan using pk_itemschedule on itemschedule itemschedule_1 (cost=0.28..0.87 rows=1 width=53) (actual time=0.013..0.013 rows=1 loops=4)

  • Index Cond: (itemscheduleguid = item_2.itemscheduleguid)
  • Filter: (recordstatusid = 1)
23. 0.108 1.880 ↑ 1.0 1 4

Nested Loop (cost=0.55..11.12 rows=1 width=200) (actual time=0.244..0.470 rows=1 loops=4)

24. 0.052 0.052 ↓ 43.0 43 4

Seq Scan on itempricelist pricelist (cost=0.00..2.54 rows=1 width=305) (actual time=0.003..0.013 rows=43 loops=4)

  • Filter: (recordstatusid = 1)
25. 1.720 1.720 ↓ 0.0 0 172

Index Scan using idx_pricelist_item_schedule on itemprice price (cost=0.55..8.58 rows=1 width=80) (actual time=0.010..0.010 rows=0 loops=172)

  • Index Cond: ((itempricelistguid = pricelist.itempricelistguid) AND (relatedmbsitem.itemguid = itemguid))
  • Filter: (recordstatusid = 1)
26. 0.001 1.493 ↓ 0.0 0 1

Nested Loop Anti Join (cost=4.36..9,850.71 rows=1 width=953) (actual time=1.493..1.493 rows=0 loops=1)

27.          

Initplan (forNested Loop Anti Join)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on itempricelist (cost=0.00..2.54 rows=1 width=148) (never executed)

  • Filter: ((itempricecode)::text = 'B85'::text)
29. 0.000 1.492 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.40..9,842.56 rows=1 width=1,005) (actual time=1.492..1.492 rows=0 loops=1)

  • Join Filter: (b85price.itempricelistguid = b85pricelist.itempricelistguid)
30. 0.000 1.492 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.40..9,839.91 rows=1 width=1,041) (actual time=1.492..1.492 rows=0 loops=1)

  • Join Filter: (b85price.itemscheduleguid = mbssch.itemscheduleguid)
31. 0.001 1.492 ↓ 0.0 0 1

Nested Loop (cost=0.85..9,839.03 rows=1 width=1,052) (actual time=1.492..1.492 rows=0 loops=1)

  • Join Filter: (mbsit.itemscheduleguid = mbssch.itemscheduleguid)
32. 0.000 1.491 ↓ 0.0 0 1

Nested Loop (cost=0.85..9,731.93 rows=95 width=1,015) (actual time=1.491..1.491 rows=0 loops=1)

33. 0.029 1.491 ↓ 0.0 0 1

Nested Loop (cost=0.42..9,429.74 rows=1 width=920) (actual time=1.491..1.491 rows=0 loops=1)

  • Join Filter: (item.itemscheduleguid = itemschedule_2.itemscheduleguid)
34. 0.307 0.307 ↑ 1.7 77 1

Seq Scan on itemschedule itemschedule_2 (cost=0.00..103.85 rows=128 width=53) (actual time=0.013..0.307 rows=77 loops=1)

  • Filter: ((itemscheduletypeid = 4) AND (recordstatusid = 1))
  • Rows Removed by Filter: 2046
35. 0.000 1.155 ↓ 0.0 0 77

Materialize (cost=0.42..9,320.14 rows=3 width=904) (actual time=0.015..0.015 rows=0 loops=77)

36. 1.172 1.172 ↓ 0.0 0 1

Index Scan using idx_tenant_recordstatus_nopatient on item (cost=0.42..9,320.13 rows=3 width=904) (actual time=1.172..1.172 rows=0 loops=1)

  • Index Cond: ((tenantguid IS NOT NULL) AND (recordstatusid = 1))
  • Filter: ((lower((itemcode)::text) = '3'::text) AND ((fee_start_date IS NULL) OR ((fee_start_date <= '2019-05-14 03:05:15.028'::timestamp without time zone) AND (item_end_date >= '2019-05-1 (...)
  • Rows Removed by Filter: 2118
37. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_item_tenant_itemcode_feestartdate on item mbsit (cost=0.42..302.17 rows=2 width=95) (never executed)

  • Index Cond: ((tenantguid IS NULL) AND (recordstatusid = 1) AND ((itemcode)::text = (item.itemcode)::text) AND (fee_start_date IS NOT NULL))
  • Filter: (item.noofpatients = noofpatients)
38. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..98.57 rows=6 width=37) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Seq Scan on itemschedule mbssch (cost=0.00..98.54 rows=6 width=37) (never executed)

  • Filter: (itemscheduletypeid = 1)
40. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_pricelist_item_schedule on itemprice b85price (cost=0.55..0.86 rows=1 width=117) (never executed)

  • Index Cond: ((itempricelistguid = $9) AND (itemguid = mbsit.itemguid))
  • Filter: (recordstatusid = 1)
41. 0.000 0.000 ↓ 0.0 0

Seq Scan on itempricelist b85pricelist (cost=0.00..2.65 rows=1 width=149) (never executed)

  • Filter: ((itempricelistguid = $9) AND (recordstatusid = 1))
42. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_itemcode_recordstatus_tenant on item relatedmbsitemnewerversion (cost=0.42..3.01 rows=1 width=52) (never executed)

  • Index Cond: ((mbsit.itemcode)::text = (itemcode)::text)
  • Filter: ((mbsit.fee_start_date < fee_start_date) AND (mbsit.noofpatients = noofpatients) AND (itemscheduleguid = mbsit.itemscheduleguid))
43. 0.001 393.603 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=0.28..85,235.56 rows=113 width=920) (actual time=393.603..393.603 rows=0 loops=1)

44. 0.014 393.602 ↓ 0.0 0 1

Nested Loop (cost=0.28..85,234.43 rows=113 width=920) (actual time=393.602..393.602 rows=0 loops=1)

45. 393.536 393.536 ↑ 159.8 4 1

Seq Scan on item item_3 (cost=0.00..84,672.86 rows=639 width=904) (actual time=177.560..393.536 rows=4 loops=1)

  • Filter: ((recordstatusid = 1) AND (lower((itemcode)::text) = '3'::text) AND ((fee_start_date IS NULL) OR ((fee_start_date <= '2019-05-14 03:05:15.028'::timestamp without time zone) AND (item_end_date >= '2019-05-14 03: (...)
  • Rows Removed by Filter: 569735
46. 0.052 0.052 ↓ 0.0 0 4

Index Scan using pk_itemschedule on itemschedule itemschedule_3 (cost=0.28..0.87 rows=1 width=53) (actual time=0.013..0.013 rows=0 loops=4)

  • Index Cond: (itemscheduleguid = item_3.itemscheduleguid)
  • Filter: ((itemscheduletypeid = 5) AND (recordstatusid = 1))
  • Rows Removed by Filter: 1
47. 0.066 404.259 ↑ 4.5 2 1

Nested Loop Left Join (cost=78.07..63,108.82 rows=9 width=1,133) (actual time=383.641..404.259 rows=2 loops=1)

48. 0.002 404.183 ↑ 4.5 2 1

Nested Loop Left Join (cost=77.78..63,085.00 rows=9 width=1,125) (actual time=383.580..404.183 rows=2 loops=1)

49. 0.016 404.171 ↑ 4.5 2 1

Nested Loop Left Join (cost=77.50..63,014.23 rows=9 width=1,114) (actual time=383.574..404.171 rows=2 loops=1)

50. 0.720 404.099 ↑ 4.5 2 1

Nested Loop (cost=76.95..62,912.98 rows=9 width=918) (actual time=383.534..404.099 rows=2 loops=1)

51. 0.013 403.240 ↑ 3.0 1 1

Nested Loop (cost=0.00..62,777.10 rows=3 width=828) (actual time=382.987..403.240 rows=1 loops=1)

  • Join Filter: (item_4.itemscheduleguid = itemschedule_4.itemscheduleguid)
  • Rows Removed by Join Filter: 4
52. 402.942 402.942 ↑ 184.6 5 1

Seq Scan on item item_4 (cost=0.00..62,595.47 rows=923 width=865) (actual time=181.089..402.942 rows=5 loops=1)

  • Filter: ((tenantguid IS NULL) AND (recordstatusid = 1) AND (lower((itemcode)::text) = '3'::text) AND ((fee_start_date IS NULL) OR ((fee_start_date <= '2019-05-14 03:05:15.028'::timestamp without time zone) AND (i (...)
  • Rows Removed by Filter: 569734
53. 0.008 0.285 ↑ 6.0 1 5

Materialize (cost=0.00..98.57 rows=6 width=37) (actual time=0.011..0.057 rows=1 loops=5)

54. 0.277 0.277 ↑ 6.0 1 1

Seq Scan on itemschedule itemschedule_4 (cost=0.00..98.54 rows=6 width=37) (actual time=0.047..0.277 rows=1 loops=1)

  • Filter: (itemscheduletypeid = 1)
  • Rows Removed by Filter: 2122
55. 0.004 0.139 ↑ 1.5 2 1

Materialize (cost=76.95..135.76 rows=3 width=90) (actual time=0.130..0.139 rows=2 loops=1)

56. 0.018 0.135 ↑ 1.5 2 1

Bitmap Heap Scan on itemschedule derivedschedule (cost=76.95..135.74 rows=3 width=90) (actual time=0.127..0.135 rows=2 loops=1)

  • Recheck Cond: (itemscheduleguid = ANY ('{960f9ca3-8a56-4cfe-a5b2-033f63f942ef,dbcc4448-02cc-4c81-b56d-742a89ef59d7,20d287fa-055a-47da-8eb3-9370ab149765,e3c7e798-c733-45e1-8ff2-4aa3af02902d,52ad6d24-51a3-e711-8180 (...)
  • Filter: ((tenantguid IS NOT NULL) AND (recordstatusid = 1) AND (itemscheduletypeid = 3))
  • Rows Removed by Filter: 29
  • Heap Blocks: exact=12
57. 0.117 0.117 ↑ 1.0 31 1

Bitmap Index Scan on pk_itemschedule (cost=0.00..76.95 rows=31 width=0) (actual time=0.117..0.117 rows=31 loops=1)

  • Index Cond: (itemscheduleguid = ANY ('{960f9ca3-8a56-4cfe-a5b2-033f63f942ef,dbcc4448-02cc-4c81-b56d-742a89ef59d7,20d287fa-055a-47da-8eb3-9370ab149765,e3c7e798-c733-45e1-8ff2-4aa3af02902d,52ad6d24-51a3-e711- (...)
58. 0.002 0.056 ↑ 1.0 1 2

Nested Loop (cost=0.55..11.23 rows=1 width=200) (actual time=0.028..0.028 rows=1 loops=2)

59. 0.020 0.020 ↑ 1.0 1 2

Seq Scan on itempricelist pricelist_1 (cost=0.00..2.65 rows=1 width=305) (actual time=0.010..0.010 rows=1 loops=2)

  • Filter: ((recordstatusid = 1) AND ((itempricecode)::text = 'SCH'::text))
  • Rows Removed by Filter: 42
60. 0.034 0.034 ↑ 1.0 1 2

Index Scan using idx_pricelist_item_schedule on itemprice price_1 (cost=0.55..8.58 rows=1 width=80) (actual time=0.016..0.017 rows=1 loops=2)

  • Index Cond: ((itempricelistguid = pricelist_1.itempricelistguid) AND (item_4.itemguid = itemguid))
  • Filter: (recordstatusid = 1)
61. 0.010 0.010 ↑ 1.0 1 2

Index Scan using pk_itemschedule on itemschedule itemscheduleref (cost=0.28..7.85 rows=1 width=48) (actual time=0.004..0.005 rows=1 loops=2)

  • Index Cond: (derivedschedule.itemscheduleguid = itemscheduleguid)
62. 0.010 0.010 ↑ 1.0 1 2

Index Scan using idx_meta_lookupguid_lookupname on lookup lk (cost=0.29..2.11 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (((lookupname)::text = 'NearestToID'::text) AND (itemscheduleref.nearesttoid = lookupvalue))
63.          

SubPlan (forSubquery Scan)

64. 0.020 0.329 ↑ 788.0 2 1

Nested Loop (cost=110.09..22,120.19 rows=1,576 width=74) (actual time=0.177..0.329 rows=2 loops=1)

65. 0.051 0.051 ↑ 31.5 6 1

Index Scan using idx_item_tenant_itemcode_feestartdate on item item1 (cost=0.42..632.88 rows=189 width=81) (actual time=0.047..0.051 rows=6 loops=1)

  • Index Cond: (tenantguid = '0ddb2280-67c8-11e8-b42f-af0946e745be'::bpchar)
66. 0.006 0.258 ↓ 0.0 0 6

Bitmap Heap Scan on item item2 (cost=109.66..113.68 rows=1 width=81) (actual time=0.043..0.043 rows=0 loops=6)

  • Recheck Cond: (((itemcode)::text = (item1.itemcode)::text) AND (tenantguid IS NULL) AND (itemscheduleguid = item1.itemscheduleguid))
  • Filter: (item1.noofpatients = noofpatients)
  • Heap Blocks: exact=2
67. 0.021 0.252 ↓ 0.0 0 6

BitmapAnd (cost=109.66..109.66 rows=1 width=0) (actual time=0.042..0.042 rows=0 loops=6)

68. 0.066 0.066 ↑ 1.9 47 6

Bitmap Index Scan on idx_itemcode_recordstatus_tenant (cost=0.00..5.17 rows=88 width=0) (actual time=0.011..0.011 rows=47 loops=6)

  • Index Cond: (((itemcode)::text = (item1.itemcode)::text) AND (tenantguid IS NULL))
69. 0.165 0.165 ↑ 10.7 393 5

Bitmap Index Scan on idx_item_itemscheduleguid (cost=0.00..104.24 rows=4,189 width=0) (actual time=0.033..0.033 rows=393 loops=5)

  • Index Cond: (itemscheduleguid = item1.itemscheduleguid)