explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QAip

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,558.082 ↓ 11.0 11 1

Limit (cost=26,091.34..26,091.35 rows=1 width=516) (actual time=1,558.080..1,558.082 rows=11 loops=1)

  • Output: rec.hmname
  • Buffers: shared hit=216024
2.          

CTE rec

3. 0.017 1,558.010 ↑ 1.0 11 1

Limit (cost=25,886.98..26,091.00 rows=11 width=393) (actual time=1,552.119..1,558.010 rows=11 loops=1)

  • Output: f.id, f.name, (COALESCE(f.id, f.parent_id)), (round((inventory.get_store_opr_jur_quantity_at_date(ss.id, ('now'::cstring)::date, true)).quantity, 3)), (round((inventory.get_store_opr_jur_quantity_at_date(ss.id, ('now'::cstring)::date, true)).mnei_quantity, 3)), ss.id, ss.type_packaging_id, hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hold.commodity_group_id, ss.store_id, (inventory.get_unit_name_by_flag_of_modif(hm.price_unit_flag, hm.id)), measure.mnemocode, (COALESCE(hm.mnei_count_in_sec_pk, 1)), hold.inn_id, ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), b.id, b.series, irl.operation_type_id
  • Buffers: shared hit=216024
4. 3.356 1,557.993 ↑ 159.3 11 1

Result (cost=25,886.98..58,382.20 rows=1,752 width=393) (actual time=1,552.117..1,557.993 rows=11 loops=1)

  • Output: f.id, f.name, (COALESCE(f.id, f.parent_id)), round((inventory.get_store_opr_jur_quantity_at_date(ss.id, ('now'::cstring)::date, true)).quantity, 3), round((inventory.get_store_opr_jur_quantity_at_date(ss.id, ('now'::cstring)::date, true)).mnei_quantity, 3), ss.id, ss.type_packaging_id, hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hold.commodity_group_id, ss.store_id, inventory.get_unit_name_by_flag_of_modif(hm.price_unit_flag, hm.id), measure.mnemocode, (COALESCE(hm.mnei_count_in_sec_pk, 1)), hold.inn_id, (SubPlan 1), (SubPlan 2), (SubPlan 3), b.id, b.series, irl.operation_type_id
  • Buffers: shared hit=216024
5. 2.528 1,551.425 ↑ 159.3 11 1

Sort (cost=25,886.98..25,891.36 rows=1,752 width=201) (actual time=1,551.421..1,551.425 rows=11 loops=1)

  • Output: f.id, f.name, (COALESCE(f.id, f.parent_id)), ss.id, ss.type_packaging_id, hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hold.commodity_group_id, ss.store_id, measure.mnemocode, (COALESCE(hm.mnei_count_in_sec_pk, 1)), hold.inn_id, b.id, b.series, irl.operation_type_id
  • Sort Key: hm.name
  • Sort Method: top-N heapsort Memory: 27kB
  • Buffers: shared hit=215362
6. 1.402 1,548.897 ↑ 2.2 781 1

Nested Loop Left Join (cost=364.52..25,847.92 rows=1,752 width=201) (actual time=2.156..1,548.897 rows=781 loops=1)

  • Output: f.id, f.name, COALESCE(f.id, f.parent_id), ss.id, ss.type_packaging_id, hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hold.commodity_group_id, ss.store_id, measure.mnemocode, COALESCE(hm.mnei_count_in_sec_pk, 1), hold.inn_id, b.id, b.series, irl.operation_type_id
  • Join Filter: (irl.id = irls.letter_id)
  • Rows Removed by Join Filter: 4686
  • Buffers: shared hit=215362
7. 1,378.883 1,546.714 ↑ 2.2 781 1

Hash Join (cost=364.52..25,689.16 rows=1,752 width=201) (actual time=2.144..1,546.714 rows=781 loops=1)

  • Output: f.id, f.name, f.parent_id, ss.id, ss.type_packaging_id, ss.store_id, b.id, b.series, hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hm.mnei_count_in_sec_pk, hold.commodity_group_id, hold.inn_id, measure.mnemocode, irls.letter_id
  • Hash Cond: (ss.funding_source_id = f.id)
  • Join Filter: ((round((inventory.get_store_opr_jur_quantity_at_date(ss.id, ('now'::cstring)::date, true)).mnei_quantity, 3) - (SubPlan 4)) <> '0'::numeric)
  • Rows Removed by Join Filter: 6865
  • Buffers: shared hit=215361
8. 3.216 114.219 ↓ 4.3 7,646 1

Hash Left Join (cost=357.65..8,981.41 rows=1,761 width=152) (actual time=1.195..114.219 rows=7,646 loops=1)

  • Output: ss.id, ss.type_packaging_id, ss.store_id, ss.funding_source_id, b.id, b.series, hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hm.mnei_count_in_sec_pk, hold.commodity_group_id, hold.inn_id, measure.mnemocode, irls.letter_id
  • Hash Cond: (b.rej_goods_journal_id = irls.rej_journal_id)
  • Buffers: shared hit=42484
9. 6.776 110.995 ↓ 4.3 7,646 1

Hash Left Join (cost=356.54..8,973.68 rows=1,761 width=152) (actual time=1.183..110.995 rows=7,646 loops=1)

  • Output: ss.id, ss.type_packaging_id, ss.store_id, ss.funding_source_id, b.id, b.series, b.rej_goods_journal_id, hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hm.mnei_count_in_sec_pk, hold.commodity_group_id, hold.inn_id, measure.mnemocode
  • Hash Cond: (hm.mnei_id = measure.id)
  • Buffers: shared hit=42483
10. 10.307 104.037 ↓ 4.3 7,646 1

Nested Loop (cost=315.42..8,908.34 rows=1,761 width=135) (actual time=0.994..104.037 rows=7,646 loops=1)

  • Output: ss.id, ss.type_packaging_id, ss.store_id, ss.funding_source_id, b.id, b.series, b.rej_goods_journal_id, hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hm.mnei_count_in_sec_pk, hold.commodity_group_id, hold.inn_id
  • Buffers: shared hit=42452
11. 5.869 38.185 ↓ 1.6 7,935 1

Nested Loop (cost=315.00..3,286.67 rows=5,047 width=119) (actual time=0.978..38.185 rows=7,935 loops=1)

  • Output: b.id, b.series, b.rej_goods_journal_id, hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hm.mnei_count_in_sec_pk, hold.commodity_group_id, hold.inn_id
  • Buffers: shared hit=10975
12. 7.086 11.520 ↑ 1.0 1,733 1

Hash Join (cost=314.71..2,143.97 rows=1,794 width=108) (actual time=0.967..11.520 rows=1,733 loops=1)

  • Output: hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hm.mnei_count_in_sec_pk, hold.commodity_group_id, hold.inn_id
  • Hash Cond: (hm.holding_id = hold.id)
  • Buffers: shared hit=1641
13. 3.681 3.681 ↓ 1.0 22,497 1

Seq Scan on inventory.hold_modif hm (cost=0.00..1,726.96 rows=22,496 width=104) (actual time=0.003..3.681 rows=22,497 loops=1)

  • Output: hm.id, hm.holding_id, hm.code, hm.form_type_id, hm.mnei_id, hm.org_owner_id, hm.name, hm.mnei_count_in_extra_pk, hm.extra_pack_unit_id, hm.extra_count_in_sec_pk, hm.sec_pack_unit_id, hm.mnei_count_in_sec_pk, hm.latin_name, hm.price_unit_flag, hm.is_extemporal, hm.write_off_unit_flag, hm.angro_flag, hm.storage_cond, hm.medicinal_purpose_flag, hm.dlo_flag, hm.prototype_id, hm.code_tfoms, hm.number_of_doses, hm.aud_who, hm.aud_who_create, hm.aud_when, hm.aud_when_create, hm.aud_source_create, hm.aud_source, hm.common_prototype_id, hm.is_vital, hm.first_pack_unit_id, hm.mnei_count_in_first_pk, hm.first_count_in_sec_pk, hm.third_one_pack_unit_id, hm.sec_count_in_third_one_pk, hm.third_two_pack_unit_id, hm.third_one_count_in_third_two_pk, hm.no_recipe_flag, hm.vzn_flag, hm.storage_cond_general, hm.is_wrtoff_unassigned, hm.from_dt, hm.to_dt
  • Buffers: shared hit=1502
14. 0.161 0.753 ↑ 1.0 1,051 1

Hash (cost=301.57..301.57 rows=1,051 width=12) (actual time=0.753..0.753 rows=1,051 loops=1)

  • Output: hold.commodity_group_id, hold.inn_id, hold.id
  • Buckets: 2048 Batches: 1 Memory Usage: 61kB
  • Buffers: shared hit=139
15. 0.508 0.592 ↑ 1.0 1,051 1

Bitmap Heap Scan on inventory.holding hold (cost=24.43..301.57 rows=1,051 width=12) (actual time=0.102..0.592 rows=1,051 loops=1)

  • Output: hold.commodity_group_id, hold.inn_id, hold.id
  • Recheck Cond: (hold.commodity_group_id = 2)
  • Heap Blocks: exact=135
  • Buffers: shared hit=139
16. 0.084 0.084 ↓ 1.0 1,054 1

Bitmap Index Scan on idx_holding (cost=0.00..24.17 rows=1,051 width=0) (actual time=0.084..0.084 rows=1,054 loops=1)

  • Index Cond: (hold.commodity_group_id = 2)
  • Buffers: shared hit=4
17. 20.796 20.796 ↑ 1.0 5 1,733

Index Scan using batch_hold_modif_id_ix on inventory.batch b (cost=0.29..0.59 rows=5 width=15) (actual time=0.005..0.012 rows=5 loops=1,733)

  • Output: b.id, b.org_id, b.hold_modif_id, b.funding_source_id, b.series, b.expire_dt, b.cert_num, b.hold_producer_id, b.country_id, b.contractor_id, b.delivery_dt, b.aud_who, b.aud_who_create, b.aud_when, b.aud_when_create, b.aud_source_create, b.aud_source, b.procurement_method_id, b.rej_goods_journal_id
  • Index Cond: (b.hold_modif_id = hm.id)
  • Buffers: shared hit=9334
18. 55.545 55.545 ↑ 1.0 1 7,935

Index Scan using idx_store_supplies_0 on inventory.store_supply ss (cost=0.42..1.10 rows=1 width=20) (actual time=0.006..0.007 rows=1 loops=7,935)

  • Output: ss.id, ss.type_packaging_id, ss.store_id, ss.funding_source_id, ss.batch_id
  • Index Cond: ((ss.batch_id = b.id) AND (ss.store_id = 4))
  • Buffers: shared hit=31477
19. 0.074 0.182 ↑ 1.0 450 1

Hash (cost=35.50..35.50 rows=450 width=21) (actual time=0.181..0.182 rows=450 loops=1)

  • Output: measure.mnemocode, measure.id
  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
  • Buffers: shared hit=31
20. 0.108 0.108 ↑ 1.0 450 1

Seq Scan on public.cmn_measure measure (cost=0.00..35.50 rows=450 width=21) (actual time=0.008..0.108 rows=450 loops=1)

  • Output: measure.mnemocode, measure.id
  • Buffers: shared hit=31
21. 0.002 0.008 ↑ 1.2 4 1

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.008..0.008 rows=4 loops=1)

  • Output: irls.rej_journal_id, irls.letter_id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
22. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on inventory.rejection_letter_spec irls (cost=0.00..1.05 rows=5 width=8) (actual time=0.005..0.006 rows=5 loops=1)

  • Output: irls.rej_journal_id, irls.letter_id
  • Buffers: shared hit=1
23. 0.042 0.090 ↓ 1.0 173 1

Hash (cost=4.72..4.72 rows=172 width=53) (actual time=0.090..0.090 rows=173 loops=1)

  • Output: f.id, f.name, f.parent_id
  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=3
24. 0.048 0.048 ↓ 1.0 173 1

Seq Scan on inventory.funding_source f (cost=0.00..4.72 rows=172 width=53) (actual time=0.010..0.048 rows=173 loops=1)

  • Output: f.id, f.name, f.parent_id
  • Buffers: shared hit=3
25.          

SubPlan (for Hash Join)

26. 7.646 53.522 ↑ 1.0 1 7,646

Aggregate (cost=9.20..9.21 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=7,646)

  • Output: COALESCE(sum(bal_1.quantity), '0'::numeric)
  • Buffers: shared hit=7646
27. 30.584 45.876 ↓ 0.0 0 7,646

Nested Loop (cost=0.15..9.19 rows=1 width=5) (actual time=0.006..0.006 rows=0 loops=7,646)

  • Output: bal_1.quantity
  • Buffers: shared hit=7646
28. 15.292 15.292 ↓ 0.0 0 7,646

Seq Scan on inventory.hold_modif_min_balance bal_1 (cost=0.00..1.02 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=7,646)

  • Output: bal_1.id, bal_1.hold_modif_id, bal_1.quantity, bal_1.store_id, bal_1.aud_who, bal_1.aud_when, bal_1.aud_source, bal_1.aud_who_create, bal_1.aud_when_create, bal_1.aud_source_create, bal_1.fund_source_id
  • Filter: ((bal_1.store_id = 4) AND (bal_1.hold_modif_id = hm.id) AND (bal_1.fund_source_id = f.id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=7646
29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using store_id_pk on inventory.store min_s_1 (cost=0.15..8.17 rows=1 width=4) (never executed)

  • Output: min_s_1.id
  • Index Cond: (min_s_1.id = 4)
  • Heap Fetches: 0
30. 0.776 0.781 ↑ 1.0 6 781

Materialize (cost=0.00..1.09 rows=6 width=8) (actual time=0.000..0.001 rows=6 loops=781)

  • Output: irl.operation_type_id, irl.id
  • Buffers: shared hit=1
31. 0.005 0.005 ↑ 1.0 6 1

Seq Scan on inventory.rejection_letter irl (cost=0.00..1.06 rows=6 width=8) (actual time=0.004..0.005 rows=6 loops=1)

  • Output: irl.operation_type_id, irl.id
  • Buffers: shared hit=1
32.          

SubPlan (for Result)

33. 0.044 0.044 ↑ 1.0 1 11

Index Scan using inn_id_pk on inventory.inn i (cost=0.28..8.30 rows=1 width=46) (actual time=0.004..0.004 rows=1 loops=11)

  • Output: i.name_rus
  • Index Cond: (i.id = hold.inn_id)
  • Buffers: shared hit=33
34. 0.022 0.077 ↑ 1.0 1 11

Aggregate (cost=9.20..9.21 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=11)

  • Output: COALESCE(sum(bal.quantity), '0'::numeric)
  • Buffers: shared hit=11
35. 0.033 0.055 ↓ 0.0 0 11

Nested Loop (cost=0.15..9.19 rows=1 width=5) (actual time=0.005..0.005 rows=0 loops=11)

  • Output: bal.quantity
  • Buffers: shared hit=11
36. 0.022 0.022 ↓ 0.0 0 11

Seq Scan on inventory.hold_modif_min_balance bal (cost=0.00..1.02 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=11)

  • Output: bal.id, bal.hold_modif_id, bal.quantity, bal.store_id, bal.aud_who, bal.aud_when, bal.aud_source, bal.aud_who_create, bal.aud_when_create, bal.aud_source_create, bal.fund_source_id
  • Filter: ((bal.store_id = 4) AND (bal.hold_modif_id = hm.id) AND (bal.fund_source_id = f.id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=11
37. 0.000 0.000 ↓ 0.0 0

Index Only Scan using store_id_pk on inventory.store min_s (cost=0.15..8.17 rows=1 width=4) (never executed)

  • Output: min_s.id
  • Index Cond: (min_s.id = 4)
  • Heap Fetches: 0
38. 3.091 3.091 ↑ 1.0 1 11

Result (cost=0.00..0.27 rows=1 width=32) (actual time=0.281..0.281 rows=1 loops=11)

  • Output: inventory.get_store_supply_price_at_date(ss.id, ('now'::cstring)::date, true)
  • Buffers: shared hit=276
39. 0.021 1,558.080 ↓ 11.0 11 1

Sort (cost=0.34..0.34 rows=1 width=516) (actual time=1,558.080..1,558.080 rows=11 loops=1)

  • Output: rec.hmname
  • Sort Key: rec.hmname
  • Sort Method: quicksort Memory: 26kB
  • Buffers: shared hit=216024
40. 1,558.059 1,558.059 ↓ 11.0 11 1

CTE Scan on rec (cost=0.00..0.33 rows=1 width=516) (actual time=1,552.129..1,558.059 rows=11 loops=1)

  • Output: rec.hmname
  • Filter: ((rec.comgroupid = 2) AND (rec.sendstoreid = 4) AND ((rec.mneiquantity - rec.minbalancemneiquantity) <> '0'::numeric))
  • Buffers: shared hit=216024
Planning time : 5.188 ms
Execution time : 1,558.353 ms