explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZW9M : Optimization for: plan #bmhc

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 4.053 2,975.655 ↓ 56.7 1,870 1

Sort (cost=124,428.71..124,428.79 rows=33 width=1,255) (actual time=2,975.539..2,975.655 rows=1,870 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: 529kB
2. 2.163 2,971.602 ↓ 56.7 1,870 1

Hash Left Join (cost=73,093.24..124,427.87 rows=33 width=1,255) (actual time=2,199.202..2,971.602 rows=1,870 loops=1)

  • Hash Cond: (ca.measure_id = m.measure_id)
3. 2.305 2,969.403 ↓ 56.7 1,870 1

Nested Loop (cost=73,091.95..124,426.47 rows=33 width=1,178) (actual time=2,199.151..2,969.403 rows=1,870 loops=1)

4. 2.200 2,959.618 ↓ 56.7 1,870 1

Nested Loop Left Join (cost=73,091.67..124,416.80 rows=33 width=1,140) (actual time=2,199.114..2,959.618 rows=1,870 loops=1)

5. 2.493 2,948.068 ↓ 56.7 1,870 1

Nested Loop (cost=73,091.39..124,404.28 rows=33 width=1,145) (actual time=2,199.091..2,948.068 rows=1,870 loops=1)

6. 3.312 2,932.485 ↓ 56.7 1,870 1

Nested Loop (cost=73,091.12..124,394.51 rows=33 width=1,109) (actual time=2,199.063..2,932.485 rows=1,870 loops=1)

  • Join Filter: (b_1.store_kpr_id = sk.store_kpr_id)
  • Rows Removed by Join Filter: 1343
7. 9.056 2,929.173 ↑ 1.2 1,870 1

Merge Join (cost=73,091.12..124,293.55 rows=2,226 width=86) (actual time=2,199.007..2,929.173 rows=1,870 loops=1)

  • Merge 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))
8. 38.293 1,630.978 ↑ 29.7 4,835 1

Finalize GroupAggregate (cost=41,452.41..86,708.43 rows=143,520 width=59) (actual time=1,145.776..1,630.978 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
9. 0.000 1,592.685 ↑ 15.6 18,385 1

Gather Merge (cost=41,452.41..79,532.43 rows=287,040 width=59) (actual time=1,144.302..1,592.685 rows=18,385 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 518.193 4,375.305 ↑ 23.4 6,128 3

Partial GroupAggregate (cost=40,452.39..45,400.87 rows=143,520 width=59) (actual time=1,093.758..1,458.435 rows=6,128 loops=3)

  • Group Key: b.acc_id, b.store_kpr_id, d.product_id
11. 2,659.110 3,857.112 ↑ 1.2 144,205 3

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

  • Sort Key: b.acc_id, b.store_kpr_id, d.product_id
  • Sort Method: external merge Disk: 6952kB
12. 578.484 1,198.002 ↑ 1.2 144,205 3

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

  • Hash Cond: (d.book_id = b.book_id)
13. 281.739 281.739 ↑ 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.128..93.913 rows=144,205 loops=3)

14. 184.455 337.779 ↑ 1.0 96,892 3

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3687kB
15. 153.324 153.324 ↑ 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.103..51.108 rows=96,892 loops=3)

16. 77.653 1,289.139 ↑ 31.3 3,963 1

Unique (cost=31,638.70..32,879.63 rows=124,093 width=27) (actual time=1,051.417..1,289.139 rows=3,963 loops=1)

17. 801.385 1,211.486 ↓ 1.0 124,157 1

Sort (cost=31,638.70..31,948.93 rows=124,093 width=27) (actual time=1,051.415..1,211.486 rows=124,157 loops=1)

  • Sort Key: b_1.acc_id, b_1.store_kpr_id, d_1.product_id
  • Sort Method: external merge Disk: 4576kB
18. 211.980 410.101 ↓ 1.0 124,221 1

Hash Join (cost=4,050.48..18,168.27 rows=124,093 width=27) (actual time=26.222..410.101 rows=124,221 loops=1)

  • Hash Cond: (d_1.book_id = b_1.book_id)
19. 174.332 174.332 ↑ 1.0 432,614 1

Seq Scan on im_dtl d_1 (cost=0.00..12,982.14 rows=432,614 width=21) (actual time=0.031..174.332 rows=432,614 loops=1)

20. 10.117 23.789 ↑ 1.0 27,764 1

Hash (cost=3,703.07..3,703.07 rows=27,793 width=30) (actual time=23.789..23.789 rows=27,764 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1971kB
21. 10.574 13.672 ↑ 1.0 27,764 1

Bitmap Heap Scan on im_book b_1 (cost=593.17..3,703.07 rows=27,793 width=30) (actual time=3.424..13.672 rows=27,764 loops=1)

  • Recheck Cond: ((book_date >= '2018-10-01'::date) AND (book_date <= '2019-03-31'::date))
  • Heap Blocks: exact=1976
22. 3.098 3.098 ↑ 1.0 27,764 1

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

  • Index Cond: ((book_date >= '2018-10-01'::date) AND (book_date <= '2019-03-31'::date))
23. 0.000 0.000 ↑ 1.5 2 1,870

Materialize (cost=0.00..1.04 rows=3 width=1,052) (actual time=0.000..0.000 rows=2 loops=1,870)

24. 0.042 0.042 ↑ 1.5 2 1

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

25. 13.090 13.090 ↑ 1.0 1 1,870

Index Scan using im_product_pk on im_product p (cost=0.28..0.30 rows=1 width=45) (actual time=0.007..0.007 rows=1 loops=1,870)

  • Index Cond: (product_id = d_1.product_id)
26. 9.350 9.350 ↑ 1.0 1 1,870

Index Scan using im_product_conf_app_pk on im_product_conf_applied ca (cost=0.28..0.38 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=1,870)

  • Index Cond: (product_id = p.product_id)
27. 7.480 7.480 ↑ 1.0 1 1,870

Index Scan using fin_acc_pk on fin_acc ac (cost=0.28..0.29 rows=1 width=56) (actual time=0.004..0.004 rows=1 loops=1,870)

  • Index Cond: (acc_id = b_1.acc_id)
28. 0.009 0.036 ↑ 1.0 13 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.027 0.027 ↑ 1.0 13 1

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

Planning time : 9.650 ms
Execution time : 2,994.219 ms