explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.674 1,483.245 ↓ 1.0 2,162 1

Sort (cost=111,237.63..111,243.02 rows=2,153 width=1,255) (actual time=1,483.135..1,483.245 rows=2,162 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: 664kB
2. 1.055 1,479.571 ↓ 1.0 2,162 1

Hash Left Join (cost=64,018.43..111,118.44 rows=2,153 width=1,255) (actual time=1,297.249..1,479.571 rows=2,162 loops=1)

  • Hash Cond: (ca.measure_id = m.measure_id)
3. 2.003 1,478.487 ↓ 1.0 2,162 1

Hash Left Join (cost=64,017.14..111,110.10 rows=2,153 width=1,178) (actual time=1,297.200..1,478.487 rows=2,162 loops=1)

  • Hash Cond: (p.product_id = ca.product_id)
4. 2.040 1,475.256 ↓ 1.0 2,162 1

Hash Join (cost=63,944.48..111,031.77 rows=2,153 width=1,183) (actual time=1,295.939..1,475.256 rows=2,162 loops=1)

  • Hash Cond: (d.product_id = p.product_id)
5. 1.773 1,471.788 ↓ 1.0 2,162 1

Hash Join (cost=63,876.82..110,958.45 rows=2,153 width=1,138) (actual time=1,294.490..1,471.788 rows=2,162 loops=1)

  • Hash Cond: (b.acc_id = ac.acc_id)
6. 1.917 1,469.266 ↓ 1.0 2,162 1

Hash Join (cost=63,847.37..110,923.31 rows=2,153 width=1,091) (actual time=1,293.642..1,469.266 rows=2,162 loops=1)

  • Hash Cond: (b.store_kpr_id = sk.store_kpr_id)
7. 13.466 1,467.316 ↑ 66.4 2,162 1

Finalize GroupAggregate (cost=63,846.30..109,102.32 rows=143,520 width=59) (actual time=1,293.590..1,467.316 rows=2,162 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: 1036
8. 0.000 1,453.850 ↑ 33.7 8,515 1

Gather Merge (cost=63,846.30..101,926.32 rows=287,040 width=59) (actual time=1,293.519..1,453.850 rows=8,515 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 248.595 4,089.354 ↑ 50.6 2,838 3

Partial GroupAggregate (cost=62,846.28..67,794.76 rows=143,520 width=59) (actual time=1,235.057..1,363.118 rows=2,838 loops=3)

  • Group Key: b.acc_id, b.store_kpr_id, d.product_id
10. 1,684.737 3,840.759 ↑ 2.2 81,657 3

Sort (cost=62,846.28..63,296.92 rows=180,256 width=35) (actual time=1,235.018..1,280.253 rows=81,657 loops=3)

  • Sort Key: b.acc_id, b.store_kpr_id, d.product_id
  • Sort Method: external merge Disk: 3968kB
11. 387.426 2,156.022 ↑ 2.2 81,657 3

Hash Join (cost=23,742.88..42,178.71 rows=180,256 width=35) (actual time=442.092..718.674 rows=81,657 loops=3)

  • Hash Cond: (d.book_id = b.book_id)
12. 258.075 1,447.554 ↑ 2.2 81,657 3

Hash Semi Join (cost=18,112.81..32,852.45 rows=180,256 width=26) (actual time=334.295..482.518 rows=81,657 loops=3)

  • Hash Cond: (d.product_id = d_1.product_id)
13. 187.212 187.212 ↑ 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.083..62.404 rows=144,205 loops=3)

14. 16.749 1,002.267 ↑ 1.2 12,259 3

Hash (cost=17,926.06..17,926.06 rows=14,940 width=9) (actual time=334.088..334.089 rows=12,259 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 625kB
15. 424.065 985.518 ↑ 1.2 12,259 3

Hash Join (cost=3,808.26..17,926.06 rows=14,940 width=9) (actual time=17.976..328.506 rows=12,259 loops=3)

  • Hash Cond: (d_1.book_id = b_1.book_id)
16. 515.424 515.424 ↑ 1.0 432,614 3

Seq Scan on im_dtl d_1 (cost=0.00..12,982.14 rows=432,614 width=21) (actual time=0.063..171.808 rows=432,614 loops=3)

17. 3.819 46.029 ↓ 1.2 3,990 3

Hash (cost=3,766.44..3,766.44 rows=3,346 width=12) (actual time=15.343..15.343 rows=3,990 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 208kB
18. 33.081 42.210 ↓ 1.2 3,990 3

Bitmap Heap Scan on im_book b_1 (cost=587.06..3,766.44 rows=3,346 width=12) (actual time=3.443..14.070 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
19. 9.129 9.129 ↑ 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.043..3.043 rows=27,764 loops=3)

  • Index Cond: ((book_date >= '2018-10-01'::date) AND (book_date <= '2019-03-31'::date))
20. 182.778 321.042 ↑ 1.0 96,892 3

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3687kB
21. 138.264 138.264 ↑ 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.041..46.088 rows=96,892 loops=3)

22. 0.007 0.033 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.026 0.026 ↑ 1.0 3 1

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

24. 0.431 0.749 ↑ 1.0 820 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 80kB
25. 0.318 0.318 ↑ 1.0 820 1

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

26. 0.879 1.428 ↑ 1.0 1,896 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 161kB
27. 0.549 0.549 ↑ 1.0 1,896 1

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

28. 0.695 1.228 ↑ 1.0 1,896 1

Hash (cost=48.96..48.96 rows=1,896 width=15) (actual time=1.228..1.228 rows=1,896 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 102kB
29. 0.533 0.533 ↑ 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.029..0.533 rows=1,896 loops=1)

30. 0.010 0.029 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=102) (actual time=0.029..0.029 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.019 0.019 ↑ 1.0 13 1

Seq Scan on info_measure m (cost=0.00..1.13 rows=13 width=102) (actual time=0.017..0.019 rows=13 loops=1)

Planning time : 7.326 ms
Execution time : 1,492.383 ms