explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bG3q

Settings
# exclusive inclusive rows x rows loops node
1. 0.131 3,290.441 ↑ 1.0 10 1

Limit (cost=124,849.96..124,849.99 rows=10 width=1,255) (actual time=3,290.311..3,290.441 rows=10 loops=1)

2. 1.372 3,290.310 ↑ 3.3 10 1

Sort (cost=124,849.96..124,850.04 rows=33 width=1,255) (actual time=3,290.310..3,290.310 rows=10 loops=1)

  • Sort Key: (sum(CASE WHEN (b.is_debit = '1'::numeric) THEN d.qty ELSE ('0'::numeric - d.qty) END)) DESC
  • Sort Method: top-N heapsort Memory: 27kB
3. 3.509 3,288.938 ↓ 56.7 1,870 1

Nested Loop Left Join (cost=73,091.67..124,849.25 rows=33 width=1,255) (actual time=2,759.450..3,288.938 rows=1,870 loops=1)

  • Join Filter: (m.measure_id = ca.measure_id)
  • Rows Removed by Join Filter: 14095
4. 449.518 3,285.429 ↓ 56.7 1,870 1

Nested Loop (cost=73,091.67..124,841.99 rows=33 width=1,178) (actual time=2,759.397..3,285.429 rows=1,870 loops=1)

  • Join Filter: (b_1.acc_id = ac.acc_id)
  • Rows Removed by Join Filter: 1531530
5. 0.351 0.351 ↑ 1.0 820 1

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

6. 79.150 2,835.560 ↓ 56.7 1,870 820

Materialize (cost=73,091.67..124,416.97 rows=33 width=1,140) (actual time=2.669..3.458 rows=1,870 loops=820)

7. 2.432 2,756.410 ↓ 56.7 1,870 1

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

8. 1.531 2,748.368 ↓ 56.7 1,870 1

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

9. 2.207 2,737.487 ↓ 56.7 1,870 1

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

  • Join Filter: (b_1.store_kpr_id = sk.store_kpr_id)
  • Rows Removed by Join Filter: 1343
10. 6.596 2,735.280 ↑ 1.2 1,870 1

Merge Join (cost=73,091.12..124,293.55 rows=2,226 width=86) (actual time=2,188.154..2,735.280 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))
11. 27.199 1,537.100 ↑ 29.7 4,835 1

Finalize GroupAggregate (cost=41,452.41..86,708.43 rows=143,520 width=59) (actual time=1,184.956..1,537.100 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
12. 0.000 1,509.901 ↑ 15.6 18,343 1

Gather Merge (cost=41,452.41..79,532.43 rows=287,040 width=59) (actual time=1,182.704..1,509.901 rows=18,343 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
13. 409.905 4,288.911 ↑ 23.5 6,114 3

Partial GroupAggregate (cost=40,452.39..45,400.87 rows=143,520 width=59) (actual time=1,135.165..1,429.637 rows=6,114 loops=3)

  • Group Key: b.acc_id, b.store_kpr_id, d.product_id
14. 2,646.330 3,879.006 ↑ 1.2 144,205 3

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

  • Sort Key: b.acc_id, b.store_kpr_id, d.product_id
  • Sort Method: external merge Disk: 6864kB
15. 607.737 1,232.676 ↑ 1.2 144,205 3

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

  • Hash Cond: (d.book_id = b.book_id)
16. 288.717 288.717 ↑ 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.145..96.239 rows=144,205 loops=3)

17. 190.383 336.222 ↑ 1.0 96,892 3

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3687kB
18. 145.839 145.839 ↑ 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.490..48.613 rows=96,892 loops=3)

19. 61.213 1,191.584 ↑ 31.3 3,963 1

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

  • Group Key: b_1.acc_id, b_1.store_kpr_id, d_1.product_id
20. 741.085 1,130.371 ↓ 1.0 124,157 1

Sort (cost=31,638.70..31,948.93 rows=124,093 width=27) (actual time=1,001.576..1,130.371 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
21. 206.737 389.286 ↓ 1.0 124,221 1

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

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

23. 12.629 29.887 ↑ 1.0 27,764 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1971kB
24. 12.360 17.258 ↑ 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.333..17.258 rows=27,764 loops=1)

  • Recheck Cond: ((book_date >= '2018-10-01'::date) AND (book_date <= '2019-03-31'::date))
  • Heap Blocks: exact=1976
25. 4.898 4.898 ↑ 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.898..4.898 rows=27,764 loops=1)

  • Index Cond: ((book_date >= '2018-10-01'::date) AND (book_date <= '2019-03-31'::date))
26. 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)

27. 0.031 0.031 ↑ 1.5 2 1

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

28. 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)
29. 5.610 5.610 ↑ 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.003..0.003 rows=1 loops=1,870)

  • Index Cond: (product_id = p.product_id)
30. 0.000 0.000 ↑ 1.6 8 1,870

Materialize (cost=0.00..1.19 rows=13 width=102) (actual time=0.000..0.000 rows=8 loops=1,870)

31. 0.031 0.031 ↑ 1.0 13 1

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