explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SmeK : mr test itempriceview lookup

Settings
# exclusive inclusive rows x rows loops node
1. 0.053 169,779.224 ↓ 2.3 7 1

Sort (cost=49,063,240.13..49,063,240.14 rows=3 width=2,879) (actual time=169,779.222..169,779.224 rows=7 loops=1)

  • Sort Key: itemview.item_end_date
  • Sort Method: quicksort Memory: 37kB
2. 0.029 169,779.171 ↓ 2.3 7 1

Subquery Scan on itemview (cost=1,126,489.61..49,063,240.11 rows=3 width=2,879) (actual time=169,096.888..169,779.171 rows=7 loops=1)

  • Filter: ((itemview.tenantguid = '1a9c2d56-85bf-11e8-b3bd-a322cf6b160c'::bpchar) OR (itemview.itemscheduletypeid = 3) OR ((itemview.tenantguid IS NULL) AND (itemview.itemscheduletypeid <> 3) AND (NOT (SubPlan 1))))
3. 0.007 783.512 ↑ 32.7 7 1

Append (cost=9,979.70..71,461.25 rows=229 width=980) (actual time=101.248..783.512 rows=7 loops=1)

4. 0.037 781.136 ↑ 45.4 5 1

Result (cost=9,979.70..66,353.42 rows=227 width=980) (actual time=101.248..781.136 rows=5 loops=1)

5. 0.006 781.099 ↑ 45.4 5 1

Append (cost=9,979.70..66,353.42 rows=227 width=980) (actual time=101.233..781.099 rows=5 loops=1)

6. 155.958 779.313 ↑ 35.2 5 1

Hash Right Join (cost=9,979.70..56,248.39 rows=176 width=994) (actual time=101.231..779.313 rows=5 loops=1)

  • Hash Cond: (price.itemguid = relatedmbsitem.itemguid)
7. 349.894 621.027 ↑ 1.3 538,398 1

Hash Join (cost=6.15..43,688.36 rows=689,141 width=63) (actual time=0.078..621.027 rows=538,398 loops=1)

  • Hash Cond: (price.itempricelistguid = pricelist.itempricelistguid)
8. 271.080 271.080 ↑ 1.3 538,398 1

Seq Scan on itemprice price (cost=0.00..34,206.53 rows=689,141 width=80) (actual time=0.010..271.080 rows=538,398 loops=1)

  • Filter: (recordstatusid = 1)
  • Rows Removed by Filter: 1022
9. 0.027 0.053 ↑ 2.0 44 1

Hash (cost=5.08..5.08 rows=86 width=57) (actual time=0.053..0.053 rows=44 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
10. 0.026 0.026 ↑ 2.0 44 1

Seq Scan on itempricelist pricelist (cost=0.00..5.08 rows=86 width=57) (actual time=0.006..0.026 rows=44 loops=1)

  • Filter: (recordstatusid = 1)
11. 0.008 2.328 ↑ 31.7 3 1

Hash (cost=9,972.36..9,972.36 rows=95 width=968) (actual time=2.328..2.328 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 3kB
12. 0.003 2.320 ↑ 31.7 3 1

Append (cost=45.33..9,972.36 rows=95 width=968) (actual time=1.567..2.320 rows=3 loops=1)

13. 0.000 0.021 ↓ 0.0 0 1

Nested Loop (cost=45.33..4,948.54 rows=3 width=967) (actual time=0.021..0.021 rows=0 loops=1)

  • Join Filter: ((item_1.itemscheduleguid = relatedmbsitem.itemscheduleguid) AND (item_1.noofpatients = relatedmbsitem.noofpatients))
14. 0.000 0.021 ↓ 0.0 0 1

Nested Loop (cost=1.00..35.17 rows=1 width=755) (actual time=0.021..0.021 rows=0 loops=1)

  • Join Filter: (item_1.itemscheduleguid = itemschedule.itemscheduleguid)
15. 0.001 0.021 ↓ 0.0 0 1

Nested Loop (cost=0.72..28.89 rows=1 width=702) (actual time=0.021..0.021 rows=0 loops=1)

16. 0.020 0.020 ↓ 0.0 0 1

Index Scan using idx_itemcode_recordstatus_tenant on item item_1 (cost=0.43..20.58 rows=1 width=665) (actual time=0.020..0.020 rows=0 loops=1)

  • Index Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1) AND (tenantguid IS NOT NULL))
  • Filter: ((fee_start_date <= '2019-03-08 00:00:00'::timestamp without time zone) AND ((item_end_date >= '2019-03-08 00:00:00'::timestamp without time zone) OR (item_end_date IS NULL)) AND (itemscheduleguid = ANY ('{74ccca27-19a5-e511-810a-0699dab61555,5046dd30-0958-4691-aace-b6e323847d60,1eca63fc-85bf-11e8-b472-7bc184bc3941,a11f75b1-4ea2-e611-8113-0699dab61555,81c3832a-68ad-e711-8180-06e473951954,18bbdd42-68ad-e711-8180-06e473951954,bb8de6af-4cf0-4934-9cee-51dbba2070c0,fd0b9db6-3aff-11e9-b587-bb314deafa5c}'::bpchar[])))
17. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_itemschedule on itemschedule relateditemschedule (cost=0.28..8.30 rows=1 width=37) (never executed)

  • Index Cond: (itemscheduleguid = item_1.itemscheduleguid)
  • Filter: (itemscheduletypeid = ANY ('{1,2,6,7}'::integer[]))
18. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_itemschedule on itemschedule (cost=0.28..6.26 rows=1 width=53) (never executed)

  • Index Cond: (itemscheduleguid = relateditemschedule.itemscheduleguid)
  • Filter: ((recordstatusid = 1) AND (itemscheduletypeid <> ALL ('{3,4,5}'::integer[])))
19. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on item relatedmbsitem (cost=44.33..4,894.67 rows=1,247 width=330) (never executed)

  • Recheck Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1) AND (tenantguid IS NULL))
20. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_itemcode_recordstatus_tenant (cost=0.00..44.02 rows=1,247 width=0) (never executed)

  • Index Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1) AND (tenantguid IS NULL))
21. 0.021 2.296 ↑ 30.7 3 1

Hash Join (cost=151.88..5,022.87 rows=92 width=930) (actual time=1.544..2.296 rows=3 loops=1)

  • Hash Cond: (item_2.itemscheduleguid = itemschedule_1.itemscheduleguid)
22. 1.069 1.333 ↑ 91.7 3 1

Bitmap Heap Scan on item item_2 (cost=44.09..4,913.13 rows=275 width=914) (actual time=0.585..1.333 rows=3 loops=1)

  • Recheck Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1) AND (tenantguid IS NULL))
  • Filter: ((fee_start_date <= '2019-03-08 00:00:00'::timestamp without time zone) AND ((item_end_date >= '2019-03-08 00:00:00'::timestamp without time zone) OR (item_end_date IS NULL)) AND (itemscheduleguid = ANY ('{74ccca27-19a5-e511-810a-0699dab61555,5046dd30-0958-4691-aace-b6e323847d60,1eca63fc-85bf-11e8-b472-7bc184bc3941,a11f75b1-4ea2-e611-8113-0699dab61555,81c3832a-68ad-e711-8180-06e473951954,18bbdd42-68ad-e711-8180-06e473951954,bb8de6af-4cf0-4934-9cee-51dbba2070c0,fd0b9db6-3aff-11e9-b587-bb314deafa5c}'::bpchar[])))
  • Rows Removed by Filter: 52
  • Heap Blocks: exact=1131
23. 0.264 0.264 ↓ 1.1 1,386 1

Bitmap Index Scan on idx_itemcode_recordstatus_tenant (cost=0.00..44.02 rows=1,247 width=0) (actual time=0.264..0.264 rows=1,386 loops=1)

  • Index Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1) AND (tenantguid IS NULL))
24. 0.294 0.942 ↑ 1.1 646 1

Hash (cost=99.10..99.10 rows=695 width=53) (actual time=0.942..0.942 rows=646 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
25. 0.648 0.648 ↑ 1.1 646 1

Seq Scan on itemschedule itemschedule_1 (cost=0.00..99.10 rows=695 width=53) (actual time=0.008..0.648 rows=646 loops=1)

  • Filter: (recordstatusid = 1)
  • Rows Removed by Filter: 1353
26. 0.000 0.031 ↓ 0.0 0 1

Nested Loop Left Join (cost=149.85..5,063.00 rows=3 width=963) (actual time=0.031..0.031 rows=0 loops=1)

  • Join Filter: (b85price.itempricelistguid = b85pricelist.itempricelistguid)
27.          

Initplan (forNested Loop Left Join)

28. 0.000 0.000 ↓ 0.0 0

Seq Scan on itempricelist (cost=0.00..5.08 rows=1 width=37) (never executed)

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

Nested Loop Left Join (cost=144.77..5,052.58 rows=3 width=999) (actual time=0.031..0.031 rows=0 loops=1)

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

Nested Loop (cost=144.22..5,027.50 rows=3 width=1,010) (actual time=0.030..0.030 rows=0 loops=1)

  • Join Filter: (item.noofpatients = mbsit.noofpatients)
31. 0.001 0.029 ↓ 0.0 0 1

Nested Loop (cost=0.72..28.89 rows=1 width=930) (actual time=0.029..0.029 rows=0 loops=1)

32. 0.028 0.028 ↓ 0.0 0 1

Index Scan using idx_itemcode_recordstatus_tenant on item (cost=0.43..20.58 rows=1 width=914) (actual time=0.028..0.028 rows=0 loops=1)

  • Index Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1) AND (tenantguid IS NOT NULL))
  • Filter: ((fee_start_date <= '2019-03-08 00:00:00'::timestamp without time zone) AND ((item_end_date >= '2019-03-08 00:00:00'::timestamp without time zone) OR (item_end_date IS NULL)) AND (itemscheduleguid = ANY ('{74ccca27-19a5-e511-810a-0699dab61555,5046dd30-0958-4691-aace-b6e323847d60,1eca63fc-85bf-11e8-b472-7bc184bc3941,a11f75b1-4ea2-e611-8113-0699dab61555,81c3832a-68ad-e711-8180-06e473951954,18bbdd42-68ad-e711-8180-06e473951954,bb8de6af-4cf0-4934-9cee-51dbba2070c0,fd0b9db6-3aff-11e9-b587-bb314deafa5c}'::bpchar[])))
33. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_itemschedule on itemschedule itemschedule_2 (cost=0.28..8.30 rows=1 width=53) (never executed)

  • Index Cond: (itemscheduleguid = item.itemscheduleguid)
  • Filter: ((itemscheduletypeid = 4) AND (recordstatusid = 1))
34. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=143.51..4,998.56 rows=4 width=87) (never executed)

  • Hash Cond: (mbsit.itemscheduleguid = mbssch.itemscheduleguid)
35. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on item mbsit (cost=44.33..4,894.67 rows=1,247 width=87) (never executed)

  • Recheck Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1) AND (tenantguid IS NULL))
36. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_itemcode_recordstatus_tenant (cost=0.00..44.02 rows=1,247 width=0) (never executed)

  • Index Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1) AND (tenantguid IS NULL))
37. 0.000 0.000 ↓ 0.0 0

Hash (cost=99.10..99.10 rows=6 width=37) (never executed)

38. 0.000 0.000 ↓ 0.0 0

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

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

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

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

Materialize (cost=0.00..5.29 rows=1 width=38) (never executed)

41. 0.000 0.000 ↓ 0.0 0

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

  • Filter: ((itempricelistguid = $5) AND (recordstatusid = 1))
42. 0.001 1.749 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=149.86..5,040.24 rows=48 width=930) (actual time=1.749..1.749 rows=0 loops=1)

43. 0.009 1.748 ↓ 0.0 0 1

Hash Join (cost=149.86..5,039.76 rows=48 width=930) (actual time=1.748..1.748 rows=0 loops=1)

  • Hash Cond: (item_3.itemscheduleguid = itemschedule_3.itemscheduleguid)
44. 1.035 1.261 ↑ 92.0 3 1

Bitmap Heap Scan on item item_3 (cost=41.02..4,929.30 rows=276 width=914) (actual time=0.533..1.261 rows=3 loops=1)

  • Recheck Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1))
  • Filter: ((fee_start_date <= '2019-03-08 00:00:00'::timestamp without time zone) AND ((item_end_date >= '2019-03-08 00:00:00'::timestamp without time zone) OR (item_end_date IS NULL)) AND (itemscheduleguid = ANY ('{74ccca27-19a5-e511-810a-0699dab61555,5046dd30-0958-4691-aace-b6e323847d60,1eca63fc-85bf-11e8-b472-7bc184bc3941,a11f75b1-4ea2-e611-8113-0699dab61555,81c3832a-68ad-e711-8180-06e473951954,18bbdd42-68ad-e711-8180-06e473951954,bb8de6af-4cf0-4934-9cee-51dbba2070c0,fd0b9db6-3aff-11e9-b587-bb314deafa5c}'::bpchar[])))
  • Rows Removed by Filter: 52
  • Heap Blocks: exact=1131
45. 0.226 0.226 ↓ 1.1 1,386 1

Bitmap Index Scan on idx_itemcode_recordstatus_tenant (cost=0.00..40.95 rows=1,252 width=0) (actual time=0.226..0.226 rows=1,386 loops=1)

  • Index Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1))
46. 0.027 0.478 ↑ 6.6 55 1

Hash (cost=104.32..104.32 rows=361 width=53) (actual time=0.478..0.478 rows=55 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
47. 0.451 0.451 ↑ 6.6 55 1

Seq Scan on itemschedule itemschedule_3 (cost=0.00..104.32 rows=361 width=53) (actual time=0.007..0.451 rows=55 loops=1)

  • Filter: ((itemscheduletypeid = 5) AND (recordstatusid = 1))
  • Rows Removed by Filter: 1944
48. 0.121 2.369 ↑ 1.0 2 1

Nested Loop Left Join (cost=174.92..5,107.81 rows=2 width=1,007) (actual time=1.061..2.369 rows=2 loops=1)

49. 0.007 2.226 ↑ 1.0 2 1

Nested Loop Left Join (cost=174.51..5,101.56 rows=2 width=999) (actual time=0.960..2.226 rows=2 loops=1)

50. 0.010 2.205 ↑ 1.0 2 1

Nested Loop Left Join (cost=174.23..5,084.95 rows=2 width=987) (actual time=0.948..2.205 rows=2 loops=1)

51. 0.050 2.007 ↑ 1.0 2 1

Nested Loop (cost=173.68..5,057.16 rows=2 width=928) (actual time=0.839..2.007 rows=2 loops=1)

52. 0.013 0.095 ↓ 2.0 2 1

Bitmap Heap Scan on itemschedule derivedschedule (cost=30.31..54.56 rows=1 width=90) (actual time=0.090..0.095 rows=2 loops=1)

  • Recheck Cond: (itemscheduleguid = ANY ('{74ccca27-19a5-e511-810a-0699dab61555,5046dd30-0958-4691-aace-b6e323847d60,1eca63fc-85bf-11e8-b472-7bc184bc3941,a11f75b1-4ea2-e611-8113-0699dab61555,81c3832a-68ad-e711-8180-06e473951954,18bbdd42-68ad-e711-8180-06e473951954,bb8de6af-4cf0-4934-9cee-51dbba2070c0,fd0b9db6-3aff-11e9-b587-bb314deafa5c}'::bpchar[]))
  • Filter: ((tenantguid IS NOT NULL) AND (recordstatusid = 1) AND (itemscheduletypeid = 3))
  • Rows Removed by Filter: 6
  • Heap Blocks: exact=8
53. 0.082 0.082 ↓ 1.9 15 1

Bitmap Index Scan on pk_itemschedule (cost=0.00..30.31 rows=8 width=0) (actual time=0.082..0.082 rows=15 loops=1)

  • Index Cond: (itemscheduleguid = ANY ('{74ccca27-19a5-e511-810a-0699dab61555,5046dd30-0958-4691-aace-b6e323847d60,1eca63fc-85bf-11e8-b472-7bc184bc3941,a11f75b1-4ea2-e611-8113-0699dab61555,81c3832a-68ad-e711-8180-06e473951954,18bbdd42-68ad-e711-8180-06e473951954,bb8de6af-4cf0-4934-9cee-51dbba2070c0,fd0b9db6-3aff-11e9-b587-bb314deafa5c}'::bpchar[]))
54. 0.025 1.862 ↑ 2.0 1 2

Hash Join (cost=143.37..5,002.57 rows=2 width=838) (actual time=0.571..0.931 rows=1 loops=2)

  • Hash Cond: (item_4.itemscheduleguid = itemschedule_4.itemscheduleguid)
55. 1.122 1.574 ↑ 33.1 21 2

Bitmap Heap Scan on item item_4 (cost=44.20..4,900.77 rows=696 width=875) (actual time=0.433..0.787 rows=21 loops=2)

  • Recheck Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1) AND (tenantguid IS NULL))
  • Filter: ((fee_start_date <= '2019-03-08 00:00:00'::timestamp without time zone) AND ((item_end_date >= '2019-03-08 00:00:00'::timestamp without time zone) OR (item_end_date IS NULL)))
  • Rows Removed by Filter: 34
  • Heap Blocks: exact=2262
56. 0.452 0.452 ↓ 1.1 1,386 2

Bitmap Index Scan on idx_itemcode_recordstatus_tenant (cost=0.00..44.02 rows=1,247 width=0) (actual time=0.226..0.226 rows=1,386 loops=2)

  • Index Cond: (((itemcode)::text = '55005'::text) AND (recordstatusid = 1) AND (tenantguid IS NULL))
57. 0.001 0.263 ↑ 6.0 1 1

Hash (cost=99.10..99.10 rows=6 width=37) (actual time=0.263..0.263 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
58. 0.262 0.262 ↑ 6.0 1 1

Seq Scan on itemschedule itemschedule_4 (cost=0.00..99.10 rows=6 width=37) (actual time=0.261..0.262 rows=1 loops=1)

  • Filter: (itemscheduletypeid = 1)
  • Rows Removed by Filter: 1998
59. 0.008 0.188 ↑ 1.0 1 2

Nested Loop (cost=0.55..13.88 rows=1 width=63) (actual time=0.091..0.094 rows=1 loops=2)

60. 0.040 0.040 ↑ 1.0 1 2

Seq Scan on itempricelist pricelist_1 (cost=0.00..5.29 rows=1 width=57) (actual time=0.018..0.020 rows=1 loops=2)

  • Filter: ((recordstatusid = 1) AND ((itempricecode)::text = 'SCH'::text))
  • Rows Removed by Filter: 43
61. 0.140 0.140 ↑ 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.070..0.070 rows=1 loops=2)

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

Index Scan using pk_itemschedule on itemschedule itemscheduleref (cost=0.28..8.30 rows=1 width=49) (actual time=0.007..0.007 rows=1 loops=2)

  • Index Cond: (derivedschedule.itemscheduleguid = itemscheduleguid)
63. 0.022 0.022 ↑ 1.0 1 2

Index Scan using idx_meta_lookupguid_lookupname on lookup lk (cost=0.41..2.58 rows=1 width=14) (actual time=0.011..0.011 rows=1 loops=2)

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

SubPlan (forSubquery Scan)

65. 0.006 168,995.630 ↓ 0.0 0 5

Materialize (cost=1,116,509.91..1,532,774.98 rows=743,801 width=74) (actual time=33,799.126..33,799.126 rows=0 loops=5)

66. 170.671 168,995.624 ↓ 0.0 0 1

Hash Join (cost=1,116,509.91..1,519,612.98 rows=743,801 width=74) (actual time=168,995.624..168,995.624 rows=0 loops=1)

  • Hash Cond: (((item1.itemcode)::text = (item2.itemcode)::text) AND (item1.itemscheduleguid = item2.itemscheduleguid) AND (item1.noofpatients = item2.noofpatients))
67. 0.031 0.031 ↑ 925.3 3 1

Index Scan using idx_tenant_recordstatus_nopatient on item item1 (cost=0.43..10,198.74 rows=2,776 width=81) (actual time=0.023..0.031 rows=3 loops=1)

  • Index Cond: (tenantguid = '1a9c2d56-85bf-11e8-b3bd-a322cf6b160c'::bpchar)
68. 374.776 168,824.922 ↑ 24.5 362,816 1

Hash (cost=839,163.46..839,163.46 rows=8,897,315 width=81) (actual time=168,824.922..168,824.922 rows=362,816 loops=1)

  • Buckets: 4096 Batches: 256 Memory Usage: 169kB
69. 168,450.146 168,450.146 ↑ 24.5 362,816 1

Seq Scan on item item2 (cost=0.00..839,163.46 rows=8,897,315 width=81) (actual time=837.238..168,450.146 rows=362,816 loops=1)

  • Filter: (tenantguid IS NULL)
  • Rows Removed by Filter: 2148