explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tjvS

Settings
# exclusive inclusive rows x rows loops node
1. 3.830 2,855.470 ↓ 56.7 1,870 1

Sort (cost=124,428.71..124,428.79 rows=33 width=1,255) (actual time=2,855.353..2,855.470 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. 1.667 2,851.640 ↓ 56.7 1,870 1

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

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

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

4. 1.117 2,842.687 ↓ 56.7 1,870 1

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

5. 2.484 2,834.090 ↓ 56.7 1,870 1

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

6. 2.583 2,822.256 ↓ 56.7 1,870 1

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

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

Merge Join (cost=73,091.12..124,293.55 rows=2,226 width=86) (actual time=2,226.542..2,819.673 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. 0.000 1,601.708 ↑ 29.7 4,835 1

Finalize GroupAggregate (cost=41,452.41..86,708.43 rows=143,520 width=59) (actual time=1,212.578..1,601.708 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,627.844 ↑ 15.6 18,386 1

Gather Merge (cost=41,452.41..79,532.43 rows=287,040 width=59) (actual time=1,208.643..1,627.844 rows=18,386 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 444.084 4,454.523 ↑ 23.4 6,129 3

Partial GroupAggregate (cost=40,452.39..45,400.87 rows=143,520 width=59) (actual time=1,166.325..1,484.841 rows=6,129 loops=3)

  • Group Key: b.acc_id, b.store_kpr_id, d.product_id
11. 2,791.440 4,010.439 ↑ 1.2 144,205 3

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

  • Sort Key: b.acc_id, b.store_kpr_id, d.product_id
  • Sort Method: external merge Disk: 6976kB
12. 609.801 1,218.999 ↑ 1.2 144,205 3

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

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

14. 187.722 329.070 ↑ 1.0 96,892 3

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

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

16. 64.011 1,210.967 ↑ 31.3 3,963 1

Group (cost=31,638.70..32,879.63 rows=124,093 width=27) (actual time=1,012.369..1,210.967 rows=3,963 loops=1)

  • Group Key: b_1.acc_id, b_1.store_kpr_id, d_1.product_id
17. 734.919 1,146.956 ↓ 1.0 124,157 1

Sort (cost=31,638.70..31,948.93 rows=124,093 width=27) (actual time=1,012.365..1,146.956 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. 208.681 412.037 ↓ 1.0 124,221 1

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

  • Hash Cond: (d_1.book_id = b_1.book_id)
19. 158.817 158.817 ↑ 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.045..158.817 rows=432,614 loops=1)

20. 20.921 44.539 ↑ 1.0 27,764 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1971kB
21. 18.696 23.618 ↑ 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=5.338..23.618 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. 4.922 4.922 ↑ 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=4.922..4.922 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.029 0.029 ↑ 1.5 2 1

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

25. 9.350 9.350 ↑ 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.005..0.005 rows=1 loops=1,870)

  • Index Cond: (product_id = d_1.product_id)
26. 7.480 7.480 ↑ 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.004..0.004 rows=1 loops=1,870)

  • Index Cond: (product_id = p.product_id)
27. 5.610 5.610 ↑ 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.003..0.003 rows=1 loops=1,870)

  • Index Cond: (acc_id = b_1.acc_id)
28. 0.016 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.020 0.020 ↑ 1.0 13 1

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

Planning time : 5.726 ms
Execution time : 2,927.448 ms