explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3MZg

Settings
# exclusive inclusive rows x rows loops node
1. 7,225.851 12,312.111 ↑ 1.1 20,960 1

Hash Left Join (cost=6,003.17..425,104.81 rows=22,076 width=393) (actual time=49.815..12,312.111 rows=20,960 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=1,408,325
2. 11.467 202.571 ↑ 1.1 20,960 1

Hash Left Join (cost=6,002.04..15,621.47 rows=22,076 width=201) (actual time=49.019..202.571 rows=20,960 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=6,230
3. 17.148 191.094 ↑ 1.1 20,960 1

Hash Left Join (cost=6,000.92..15,537.56 rows=22,076 width=201) (actual time=49.002..191.094 rows=20,960 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=6,229
4. 21.313 173.757 ↑ 1.1 20,960 1

Hash Join (cost=5,959.80..15,192.93 rows=22,076 width=184) (actual time=48.798..173.757 rows=20,960 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: (hm.holding_id = hold.id)
  • Buffers: shared hit=6,198
5. 26.569 147.491 ↑ 1.1 20,960 1

Hash Join (cost=5,399.29..14,328.88 rows=22,076 width=180) (actual time=43.819..147.491 rows=20,960 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, hm.holding_id
  • Hash Cond: (b.hold_modif_id = hm.id)
  • Buffers: shared hit=5,934
6. 26.476 107.973 ↑ 1.1 20,960 1

Hash Join (cost=3,391.13..12,017.17 rows=22,076 width=80) (actual time=30.826..107.973 rows=20,960 loops=1)

  • Output: f.id, f.name, f.parent_id, ss.id, ss.type_packaging_id, ss.store_id, b.id, b.series, b.hold_modif_id, b.rej_goods_journal_id
  • Hash Cond: (ss.batch_id = b.id)
  • Buffers: shared hit=4,432
7. 17.894 52.965 ↑ 1.1 20,960 1

Hash Join (cost=1,042.38..9,364.87 rows=22,076 width=69) (actual time=2.210..52.965 rows=20,960 loops=1)

  • Output: f.id, f.name, f.parent_id, ss.id, ss.type_packaging_id, ss.store_id, ss.batch_id
  • Hash Cond: (ss.funding_source_id = f.id)
  • Buffers: shared hit=3,507
8. 33.327 34.980 ↑ 1.1 20,960 1

Bitmap Heap Scan on inventory.store_supply ss (cost=1,035.51..9,054.46 rows=22,076 width=20) (actual time=2.093..34.980 rows=20,960 loops=1)

  • Output: ss.id, ss.type_packaging_id, ss.store_id, ss.funding_source_id, ss.batch_id
  • Recheck Cond: (ss.store_id = 4)
  • Heap Blocks: exact=3,426
  • Buffers: shared hit=3,504
9. 1.653 1.653 ↑ 1.1 20,960 1

Bitmap Index Scan on store_supply_store_id_ix (cost=0.00..1,029.99 rows=22,076 width=0) (actual time=1.653..1.653 rows=20,960 loops=1)

  • Index Cond: (ss.store_id = 4)
  • Buffers: shared hit=78
10. 0.030 0.091 ↓ 1.0 173 1

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

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

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

  • Output: f.id, f.name, f.parent_id
  • Buffers: shared hit=3
12. 11.310 28.532 ↓ 1.0 63,325 1

Hash (cost=1,557.78..1,557.78 rows=63,278 width=15) (actual time=28.532..28.532 rows=63,325 loops=1)

  • Output: b.id, b.series, b.hold_modif_id, b.rej_goods_journal_id
  • Buckets: 65,536 Batches: 1 Memory Usage: 3,359kB
  • Buffers: shared hit=925
13. 17.222 17.222 ↓ 1.0 63,325 1

Seq Scan on inventory.batch b (cost=0.00..1,557.78 rows=63,278 width=15) (actual time=0.011..17.222 rows=63,325 loops=1)

  • Output: b.id, b.series, b.hold_modif_id, b.rej_goods_journal_id
  • Buffers: shared hit=925
14. 5.457 12.949 ↓ 1.0 22,497 1

Hash (cost=1,726.96..1,726.96 rows=22,496 width=104) (actual time=12.949..12.949 rows=22,497 loops=1)

  • Output: hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hm.mnei_count_in_sec_pk, hm.holding_id
  • Buckets: 32,768 Batches: 1 Memory Usage: 3,276kB
  • Buffers: shared hit=1,502
15. 7.492 7.492 ↓ 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.007..7.492 rows=22,497 loops=1)

  • Output: hm.id, hm.name, hm.price_unit_flag, hm.mnei_id, hm.mnei_count_in_sec_pk, hm.holding_id
  • Buffers: shared hit=1,502
16. 1.942 4.953 ↓ 1.0 13,181 1

Hash (cost=395.78..395.78 rows=13,178 width=12) (actual time=4.953..4.953 rows=13,181 loops=1)

  • Output: hold.commodity_group_id, hold.inn_id, hold.id
  • Buckets: 16,384 Batches: 1 Memory Usage: 662kB
  • Buffers: shared hit=264
17. 3.011 3.011 ↓ 1.0 13,181 1

Seq Scan on inventory.holding hold (cost=0.00..395.78 rows=13,178 width=12) (actual time=0.010..3.011 rows=13,181 loops=1)

  • Output: hold.commodity_group_id, hold.inn_id, hold.id
  • Buffers: shared hit=264
18. 0.089 0.189 ↑ 1.0 450 1

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

  • Output: measure.mnemocode, measure.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 33kB
  • Buffers: shared hit=31
19. 0.100 0.100 ↑ 1.0 450 1

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

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

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

  • Output: irls.rej_journal_id, irls.letter_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
21. 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.006..0.008 rows=5 loops=1)

  • Output: irls.rej_journal_id, irls.letter_id
  • Buffers: shared hit=1
22. 0.002 0.009 ↑ 1.0 6 1

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

  • Output: irl.operation_type_id, irl.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
23. 0.007 0.007 ↑ 1.0 6 1

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

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

SubPlan (for Hash Left Join)

25. 41.920 41.920 ↓ 0.0 0 20,960

Index Scan using inn_id_pk on inventory.inn i (cost=0.28..8.30 rows=1 width=46) (actual time=0.002..0.002 rows=0 loops=20,960)

  • Output: i.name_rus
  • Index Cond: (i.id = hold.inn_id)
  • Buffers: shared hit=22,463
26. 20.960 146.720 ↑ 1.0 1 20,960

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

  • Output: COALESCE(sum(bal.quantity), '0'::numeric)
  • Buffers: shared hit=20,960
27. 83.840 125.760 ↓ 0.0 0 20,960

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

  • Output: bal.quantity
  • Buffers: shared hit=20,960
28. 41.920 41.920 ↓ 0.0 0 20,960

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=20,960)

  • 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=20,960
29. 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
30. 4,695.040 4,695.040 ↑ 1.0 1 20,960

Result (cost=0.00..0.27 rows=1 width=32) (actual time=0.224..0.224 rows=1 loops=20,960)

  • Output: inventory.get_store_supply_price_at_date(ss.id, ('now'::cstring)::date, true)
  • Buffers: shared hit=502,290
Planning time : 2.340 ms
Execution time : 12,315.534 ms