explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YDFN

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,956.940 ↓ 11.0 11 1

Limit (cost=58,240.12..58,240.12 rows=1 width=516) (actual time=1,956.936..1,956.940 rows=11 loops=1)

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

CTE rec

3. 251.712 1,951.329 ↑ 2.2 781 1

Hash Left Join (cost=365.66..58,187.55 rows=1,752 width=393) (actual time=3.243..1,951.329 rows=781 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
  • Hash Cond: (irls.letter_id = irl.id)
  • Buffers: shared hit=270962
4. 0.576 1,452.034 ↑ 2.2 781 1

Hash Left Join (cost=364.52..25,689.00 rows=1,752 width=201) (actual time=2.184..1,452.034 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: (b.rej_goods_journal_id = irls.rej_journal_id)
  • Buffers: shared hit=215361
5. 1.062 1,451.449 ↑ 2.2 781 1

Hash Left Join (cost=363.41..25,681.31 rows=1,752 width=201) (actual time=2.172..1,451.449 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, 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=215360
6. 1,295.237 1,450.204 ↑ 2.2 781 1

Hash Join (cost=322.29..25,616.10 rows=1,752 width=184) (actual time=1.983..1,450.204 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, 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
  • 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=215329
7. 8.849 101.358 ↓ 4.3 7,646 1

Nested Loop (cost=315.42..8,908.34 rows=1,761 width=135) (actual time=1.120..101.358 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
8. 6.371 36.964 ↓ 1.6 7,935 1

Nested Loop (cost=315.00..3,286.67 rows=5,047 width=119) (actual time=1.098..36.964 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
9. 7.090 11.530 ↑ 1.0 1,733 1

Hash Join (cost=314.71..2,143.97 rows=1,794 width=108) (actual time=1.082..11.530 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
10. 3.607 3.607 ↓ 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.607 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
11. 0.178 0.833 ↑ 1.0 1,051 1

Hash (cost=301.57..301.57 rows=1,051 width=12) (actual time=0.833..0.833 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
12. 0.572 0.655 ↑ 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.112..0.655 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
13. 0.083 0.083 ↓ 1.0 1,054 1

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

  • Index Cond: (hold.commodity_group_id = 2)
  • Buffers: shared hit=4
14. 19.063 19.063 ↑ 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.011 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
15. 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
16. 0.032 0.087 ↓ 1.0 173 1

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

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

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

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

SubPlan (for Hash Join)

19. 15.292 53.522 ↑ 1.0 1 7,646

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

  • Output: COALESCE(sum(bal_1.quantity), '0'::numeric)
  • Buffers: shared hit=7646
20. 22.938 38.230 ↓ 0.0 0 7,646

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

  • Output: bal_1.quantity
  • Buffers: shared hit=7646
21. 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
22. 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
23. 0.065 0.183 ↑ 1.0 450 1

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

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

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

  • Output: measure.mnemocode, measure.id
  • Buffers: shared hit=31
25. 0.001 0.009 ↑ 1.2 4 1

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

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

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

  • Output: irls.rej_journal_id, irls.letter_id
  • Buffers: shared hit=1
27. 0.001 0.006 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.006..0.006 rows=6 loops=1)

  • Output: irl.operation_type_id, irl.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
28. 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.003..0.005 rows=6 loops=1)

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

SubPlan (for Hash Left Join)

30. 3.124 3.124 ↑ 1.0 1 781

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=781)

  • Output: i.name_rus
  • Index Cond: (i.id = hold.inn_id)
  • Buffers: shared hit=2201
31. 0.781 5.467 ↑ 1.0 1 781

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

  • Output: COALESCE(sum(bal.quantity), '0'::numeric)
  • Buffers: shared hit=781
32. 3.124 4.686 ↓ 0.0 0 781

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

  • Output: bal.quantity
  • Buffers: shared hit=781
33. 1.562 1.562 ↓ 0.0 0 781

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=781)

  • 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=781
34. 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
35. 238.986 238.986 ↑ 1.0 1 781

Result (cost=0.00..0.27 rows=1 width=32) (actual time=0.306..0.306 rows=1 loops=781)

  • Output: inventory.get_store_supply_price_at_date(ss.id, ('now'::cstring)::date, true)
  • Buffers: shared hit=23966
36. 1.930 1,956.936 ↓ 11.0 11 1

Sort (cost=52.57..52.57 rows=1 width=516) (actual time=1,956.936..1,956.936 rows=11 loops=1)

  • Output: rec.hmname
  • Sort Key: rec.hmname
  • Sort Method: top-N heapsort Memory: 26kB
  • Buffers: shared hit=270962
37. 1,955.006 1,955.006 ↓ 781.0 781 1

CTE Scan on rec (cost=0.00..52.56 rows=1 width=516) (actual time=3.250..1,955.006 rows=781 loops=1)

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