explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bmhc

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 23.647 2,095.977 ↓ 1.2 2,618 1

Sort (cost=124,452.91..124,458.29 rows=2,153 width=1,429) (actual time=2,095.806..2,095.977 rows=2,618 loops=1)

  • Sort Key: ac.acc_code, kpr.store_kpr_code, pr.product_code
  • Sort Method: quicksort Memory: 795kB
2. 6.909 2,072.330 ↓ 1.2 2,618 1

Hash Left Join (cost=42,765.36..124,333.72 rows=2,153 width=1,429) (actual time=1,302.921..2,072.330 rows=2,618 loops=1)

  • Hash Cond: (d.product_id = eq.product_id)
3. 2.786 2,065.378 ↓ 1.2 2,618 1

Hash Join (cost=42,760.81..124,248.04 rows=2,153 width=1,397) (actual time=1,302.862..2,065.378 rows=2,618 loops=1)

  • Hash Cond: (d.product_id = pr.product_id)
4. 2.933 2,061.279 ↓ 1.2 2,618 1

Hash Join (cost=42,693.15..124,174.72 rows=2,153 width=1,362) (actual time=1,301.526..2,061.279 rows=2,618 loops=1)

  • Hash Cond: (b.acc_id = ac.acc_id)
5. 2.165 2,057.769 ↓ 1.2 2,618 1

Merge Join (cost=42,663.70..124,139.58 rows=2,153 width=1,315) (actual time=1,300.853..2,057.769 rows=2,618 loops=1)

  • Merge Cond: (b.store_kpr_id = kpr.store_kpr_id)
6. 117.781 2,055.547 ↑ 54.8 2,618 1

Finalize GroupAggregate (cost=42,662.64..122,322.98 rows=143,520 width=283) (actual time=1,300.792..2,055.547 rows=2,618 loops=1)

  • Group Key: b.store_kpr_id, b.acc_id, d.product_id
  • Filter: ((sum(CASE WHEN ((b.book_date >= '2019-02-07'::date) AND (b.is_debit = '1'::numeric)) THEN d.qty ELSE '0'::numeric END) <> '0'::numeric) OR (sum(CASE WHEN ((b.book_date >= '2019-02-07'::date) AND (b.is_debit = '1'::numeric)) THEN d.total_cost ELSE '0'::numeric END) <> '0'::numeric) OR (sum(CASE WHEN ((b.book_date >= '2019-02-07'::date) AND (b.is_debit = '0'::numeric)) THEN d.qty ELSE '0'::numeric END) <> '0'::numeric) OR (sum(CASE WHEN ((b.book_date >= '2019-02-07'::date) AND (b.is_debit = '0'::numeric)) THEN d.total_cost ELSE '0'::numeric END) <> '0'::numeric) OR (sum(CASE WHEN (b.book_date < '2019-02-07'::date) THEN CASE WHEN (b.is_debit = '1'::numeric) THEN d.qty ELSE ('0'::numeric - d.qty) END ELSE '0'::numeric END) <> '0'::numeric) OR (sum(CASE WHEN (b.book_date < '2019-02-07'::date) THEN CASE WHEN (b.is_debit = '1'::numeric) THEN d.total_cost ELSE ('0'::numeric - d.total_cost) END ELSE '0'::numeric END) <> '0'::numeric))
  • Rows Removed by Filter: 4814
7. 0.000 1,937.766 ↑ 15.6 18,401 1

Gather Merge (cost=42,662.64..93,618.98 rows=287,040 width=283) (actual time=1,300.022..1,937.766 rows=18,401 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
8. 1,201.110 5,480.394 ↑ 23.4 6,134 3

Partial GroupAggregate (cost=41,662.62..59,487.42 rows=143,520 width=283) (actual time=1,256.867..1,826.798 rows=6,134 loops=3)

  • Group Key: b.store_kpr_id, b.acc_id, d.product_id
9. 2,967.219 4,279.284 ↑ 1.2 144,205 3

Sort (cost=41,662.62..42,113.26 rows=180,256 width=46) (actual time=1,256.189..1,426.428 rows=144,205 loops=3)

  • Sort Key: b.store_kpr_id, b.acc_id, d.product_id
  • Sort Method: external merge Disk: 8504kB
10. 654.843 1,312.065 ↑ 1.2 144,205 3

Hash Join (cost=5,872.30..20,379.05 rows=180,256 width=46) (actual time=117.527..437.355 rows=144,205 loops=3)

  • Hash Cond: (d.book_id = b.book_id)
11. 307.125 307.125 ↑ 1.2 144,205 3

Parallel Seq Scan on im_dtl d (cost=0.00..10,458.56 rows=180,256 width=33) (actual time=0.123..102.375 rows=144,205 loops=3)

12. 190.023 350.097 ↑ 1.0 96,892 3

Hash (cost=3,904.15..3,904.15 rows=96,892 width=37) (actual time=116.699..116.699 rows=96,892 loops=3)

  • Buckets: 65536 Batches: 2 Memory Usage: 3972kB
13. 160.074 160.074 ↑ 1.0 96,892 3

Seq Scan on im_book b (cost=0.00..3,904.15 rows=96,892 width=37) (actual time=0.106..53.358 rows=96,892 loops=3)

  • Filter: (book_date <= '2019-02-07'::date)
14. 0.026 0.057 ↑ 1.5 2 1

Sort (cost=1.05..1.06 rows=3 width=1,052) (actual time=0.057..0.057 rows=2 loops=1)

  • Sort Key: kpr.store_kpr_id
  • Sort Method: quicksort Memory: 25kB
15. 0.031 0.031 ↑ 1.0 3 1

Seq Scan on im_store_kpr kpr (cost=0.00..1.03 rows=3 width=1,052) (actual time=0.029..0.031 rows=3 loops=1)

16. 0.357 0.577 ↑ 1.0 820 1

Hash (cost=19.20..19.20 rows=820 width=56) (actual time=0.577..0.577 rows=820 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 80kB
17. 0.220 0.220 ↑ 1.0 820 1

Seq Scan on fin_acc ac (cost=0.00..19.20 rows=820 width=56) (actual time=0.018..0.220 rows=820 loops=1)

18. 0.813 1.313 ↑ 1.0 1,896 1

Hash (cost=43.96..43.96 rows=1,896 width=45) (actual time=1.313..1.313 rows=1,896 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
19. 0.500 0.500 ↑ 1.0 1,896 1

Seq Scan on im_product pr (cost=0.00..43.96 rows=1,896 width=45) (actual time=0.025..0.500 rows=1,896 loops=1)

20. 0.000 0.043 ↓ 0.0 0 1

Hash (cost=4.54..4.54 rows=1 width=14) (actual time=0.043..0.043 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
21. 0.043 0.043 ↓ 0.0 0 1

Seq Scan on im_product_eq eq (cost=0.00..4.54 rows=1 width=14) (actual time=0.043..0.043 rows=0 loops=1)

  • Filter: (eq_measure_id = '-1'::numeric)
  • Rows Removed by Filter: 203