explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xxgK : Optimization for: Optimization for: plan #bmhc; plan #ZW9M

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.816 1,897.107 ↓ 208.2 833 1

Sort (cost=106,633.87..106,633.88 rows=4 width=1,255) (actual time=1,897.062..1,897.107 rows=833 loops=1)

  • Sort Key: (sum(CASE WHEN (b.is_debit = '1'::numeric) THEN d.qty ELSE ('0'::numeric - d.qty) END)) DESC
  • Sort Method: quicksort Memory: 245kB
2. 1.469 1,895.291 ↓ 208.2 833 1

Nested Loop Left Join (cost=59,534.69..106,633.83 rows=4 width=1,255) (actual time=1,587.062..1,895.291 rows=833 loops=1)

3. 1.222 1,892.156 ↓ 208.2 833 1

Hash Join (cost=59,534.55..106,633.19 rows=4 width=1,178) (actual time=1,587.049..1,892.156 rows=833 loops=1)

  • Hash Cond: (d_1.product_id = p.product_id)
4. 4.103 1,886.992 ↓ 208.2 833 1

Hash Join (cost=59,389.25..106,487.83 rows=4 width=1,147) (actual time=1,583.088..1,886.992 rows=833 loops=1)

  • Hash Cond: ((b.acc_id = b_1.acc_id) AND (b.store_kpr_id = b_1.store_kpr_id) AND (d.product_id = d_1.product_id))
5. 4.735 1,678.007 ↓ 2.2 4,835 1

Hash Join (cost=41,482.93..88,564.56 rows=2,153 width=1,167) (actual time=1,250.365..1,678.007 rows=4,835 loops=1)

  • Hash Cond: (b.acc_id = ac.acc_id)
6. 5.239 1,672.602 ↓ 2.2 4,835 1

Hash Join (cost=41,453.48..88,529.43 rows=2,153 width=1,111) (actual time=1,249.675..1,672.602 rows=4,835 loops=1)

  • Hash Cond: (b.store_kpr_id = sk.store_kpr_id)
7. 22.582 1,667.328 ↑ 29.7 4,835 1

Finalize GroupAggregate (cost=41,452.41..86,708.43 rows=143,520 width=59) (actual time=1,249.618..1,667.328 rows=4,835 loops=1)

  • Group Key: b.acc_id, b.store_kpr_id, d.product_id
  • Filter: (sum(CASE WHEN (b.is_debit = '1'::numeric) THEN d.qty ELSE ('0'::numeric - d.qty) END) = '0'::numeric)
  • Rows Removed by Filter: 2597
8. 0.000 1,644.746 ↑ 15.6 18,425 1

Gather Merge (cost=41,452.41..79,532.43 rows=287,040 width=59) (actual time=1,247.038..1,644.746 rows=18,425 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 464.796 4,625.934 ↑ 23.4 6,142 3

Partial GroupAggregate (cost=40,452.39..45,400.87 rows=143,520 width=59) (actual time=1,207.329..1,541.978 rows=6,142 loops=3)

  • Group Key: b.acc_id, b.store_kpr_id, d.product_id
10. 2,875.071 4,161.138 ↑ 1.2 144,205 3

Sort (cost=40,452.39..40,903.03 rows=180,256 width=35) (actual time=1,206.827..1,387.046 rows=144,205 loops=3)

  • Sort Key: b.acc_id, b.store_kpr_id, d.product_id
  • Sort Method: external merge Disk: 6832kB
11. 636.855 1,286.067 ↑ 1.2 144,205 3

Hash Join (cost=5,630.07..19,784.82 rows=180,256 width=35) (actual time=117.943..428.689 rows=144,205 loops=3)

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

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

13. 198.222 351.279 ↑ 1.0 96,892 3

Hash (cost=3,661.92..3,661.92 rows=96,892 width=33) (actual time=117.093..117.093 rows=96,892 loops=3)

  • Buckets: 65536 Batches: 2 Memory Usage: 3687kB
14. 153.057 153.057 ↑ 1.0 96,892 3

Seq Scan on im_book b (cost=0.00..3,661.92 rows=96,892 width=33) (actual time=0.097..51.019 rows=96,892 loops=3)

15. 0.014 0.035 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=1,052) (actual time=0.034..0.035 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
16. 0.021 0.021 ↑ 1.0 3 1

Seq Scan on im_store_kpr sk (cost=0.00..1.03 rows=3 width=1,052) (actual time=0.020..0.021 rows=3 loops=1)

17. 0.387 0.670 ↑ 1.0 820 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 80kB
18. 0.283 0.283 ↑ 1.0 820 1

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

19. 1.158 204.882 ↑ 9.8 1,518 1

Hash (cost=17,644.86..17,644.86 rows=14,940 width=27) (actual time=204.882..204.882 rows=1,518 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 218kB
20. 16.581 203.724 ↑ 9.8 1,518 1

HashAggregate (cost=17,346.06..17,495.46 rows=14,940 width=27) (actual time=202.942..203.724 rows=1,518 loops=1)

  • Group Key: b_1.acc_id, b_1.store_kpr_id, d_1.product_id
21. 32.199 187.143 ↑ 1.2 12,259 1

Gather (cost=4,808.26..17,234.01 rows=14,940 width=27) (actual time=39.885..187.143 rows=12,259 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
22. 53.372 154.944 ↑ 1.5 4,086 3

Hash Join (cost=3,808.26..14,740.01 rows=6,225 width=27) (actual time=31.297..154.944 rows=4,086 loops=3)

  • Hash Cond: (d_1.book_id = b_1.book_id)
23. 75.556 75.556 ↑ 1.2 144,205 3

Parallel Seq Scan on im_dtl d_1 (cost=0.00..10,458.56 rows=180,256 width=21) (actual time=0.273..75.556 rows=144,205 loops=3)

24. 2.150 26.016 ↓ 1.2 3,990 3

Hash (cost=3,766.44..3,766.44 rows=3,346 width=30) (actual time=26.015..26.016 rows=3,990 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 281kB
25. 20.491 23.866 ↓ 1.2 3,990 3

Bitmap Heap Scan on im_book b_1 (cost=587.06..3,766.44 rows=3,346 width=30) (actual time=3.916..23.866 rows=3,990 loops=3)

  • Recheck Cond: ((book_date >= '2018-10-01'::date) AND (book_date <= '2019-03-31'::date))
  • Filter: (book_type_id = '6'::numeric)
  • Rows Removed by Filter: 23774
  • Heap Blocks: exact=1976
26. 3.375 3.375 ↑ 1.0 27,764 3

Bitmap Index Scan on im_book_idx_date (cost=0.00..586.22 rows=27,793 width=0) (actual time=3.375..3.375 rows=27,764 loops=3)

  • Index Cond: ((book_date >= '2018-10-01'::date) AND (book_date <= '2019-03-31'::date))
27. 0.820 3.942 ↑ 1.0 1,896 1

Hash (cost=121.61..121.61 rows=1,896 width=50) (actual time=3.942..3.942 rows=1,896 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 169kB
28. 1.451 3.122 ↑ 1.0 1,896 1

Hash Right Join (cost=67.66..121.61 rows=1,896 width=50) (actual time=1.374..3.122 rows=1,896 loops=1)

  • Hash Cond: (ca.product_id = p.product_id)
29. 0.333 0.333 ↑ 1.0 1,896 1

Seq Scan on im_product_conf_applied ca (cost=0.00..48.96 rows=1,896 width=15) (actual time=0.018..0.333 rows=1,896 loops=1)

30. 0.824 1.338 ↑ 1.0 1,896 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
31. 0.514 0.514 ↑ 1.0 1,896 1

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

32. 1.666 1.666 ↓ 0.0 0 833

Index Scan using info_measure_pk on info_measure m (cost=0.14..0.16 rows=1 width=102) (actual time=0.002..0.002 rows=0 loops=833)

  • Index Cond: (measure_id = ca.measure_id)
Planning time : 5.228 ms
Execution time : 1,918.080 ms