explain.depesz.com

A tool for finding a real cause for slow queries.

Result: IlGO

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 0.315 16,842.391 ↑ 2,594.6 32 1

Sort (cost=8,141,882.12..8,142,089.69 rows=83,028 width=181) (actual time=16,842.383..16,842.391 rows=32 loops=1)

  • Sort Key: stock.part, stock.company, stock.depot
  • Sort Method: quicksort Memory: 39kB
2. 2,807.663 16,842.076 ↑ 2,594.6 32 1

Hash Join (cost=120,017.72..8,135,098.19 rows=83,028 width=181) (actual time=10,712.434..16,842.076 rows=32 loops=1)

  • Hash Cond: (stprice.stockid = stock.stockid)
3. 3,337.813 3,337.813 ↑ 1.0 5,608,223 1

Seq Scan on stprice (cost=0.00..203,621.42 rows=5,608,242 width=13) (actual time=0.013..3,337.813 rows=5,608,223 loops=1)

4. 0.279 10,688.696 ↑ 2,594.6 32 1

Hash (cost=118,979.87..118,979.87 rows=83,028 width=172) (actual time=10,688.696..10,688.696 rows=32 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 8kB
5. 227.401 10,688.417 ↑ 2,594.6 32 1

Merge Left Join (cost=112,411.09..118,979.87 rows=83,028 width=172) (actual time=10,680.599..10,688.417 rows=32 loops=1)

  • Merge Cond: (((stock.product_group)::text = (grpdesc.id)::text) AND (stock.company = grpdesc.company))
6. 0.218 436.163 ↑ 2,594.2 32 1

Sort (cost=10,525.60..10,733.13 rows=83,014 width=150) (actual time=436.148..436.163 rows=32 loops=1)

  • Sort Key: stock.product_group, stock.company
  • Sort Method: quicksort Memory: 33kB
7. 0.408 435.945 ↑ 2,594.2 32 1

Nested Loop (cost=23.34..3,742.91 rows=83,014 width=150) (actual time=434.505..435.945 rows=32 loops=1)

  • Join Filter: ((stock.part)::text = fn_stksrch_grep_perl.fn_stksrch_grep_perl)
8. 0.095 433.888 ↑ 11.8 17 1

HashAggregate (cost=12.75..14.75 rows=200 width=32) (actual time=433.871..433.888 rows=17 loops=1)

9. 433.793 433.793 ↑ 58.8 17 1

Function Scan on fn_stksrch_grep_perl (cost=0.25..10.25 rows=1,000 width=32) (actual time=433.781..433.793 rows=17 loops=1)

10. 0.289 1.649 ↑ 1.0 2 17

Bitmap Heap Scan on stock (cost=10.59..18.62 rows=2 width=150) (actual time=0.091..0.097 rows=2 loops=17)

  • Recheck Cond: (((company = 1) AND ((depot)::text = 01::text) AND ((part)::text = fn_stksrch_grep_perl.fn_stksrch_grep_perl)) OR ((company = 81) AND ((depot)::text = 81::text) AND ((part)::text = fn_stksrch_grep_perl.fn_stksrch_grep_perl)))
11. 0.068 1.360 ↓ 0.0 0 17

BitmapOr (cost=10.59..10.59 rows=2 width=0) (actual time=0.080..0.080 rows=0 loops=17)

12. 0.748 0.748 ↑ 1.0 1 17

Bitmap Index Scan on stock_idx1 (cost=0.00..5.29 rows=1 width=0) (actual time=0.044..0.044 rows=1 loops=17)

  • Index Cond: ((company = 1) AND ((depot)::text = 01::text) AND ((part)::text = fn_stksrch_grep_perl.fn_stksrch_grep_perl))
13. 0.544 0.544 ↑ 2.0 1 17

Bitmap Index Scan on stock_idx1 (cost=0.00..5.29 rows=2 width=0) (actual time=0.032..0.032 rows=1 loops=17)

  • Index Cond: ((company = 81) AND ((depot)::text = 81::text) AND ((part)::text = fn_stksrch_grep_perl.fn_stksrch_grep_perl))
14. 8,594.172 10,024.853 ↑ 2.1 328,042 1

Sort (cost=101,778.57..103,535.01 rows=702,575 width=30) (actual time=9,746.386..10,024.853 rows=328,042 loops=1)

  • Sort Key: grpdesc.id, grpdesc.company
  • Sort Method: external sort Disk: 32200kB
15. 1,430.681 1,430.681 ↓ 1.0 703,420 1

Seq Scan on grpdesc (cost=0.00..33,550.49 rows=702,575 width=30) (actual time=0.042..1,430.681 rows=703,420 loops=1)

  • Filter: ((record_type)::text = G::text)
16.          

SubPlan (forHash Join)

17. 0.224 1.408 ↑ 1.0 1 32

Aggregate (cost=13.37..13.38 rows=1 width=8) (actual time=0.044..0.044 rows=1 loops=32)

18. 1.184 1.184 ↑ 2.0 1 32

Index Scan using stock_idx1 on stock a (cost=0.00..13.36 rows=2 width=8) (actual time=0.036..0.037 rows=1 loops=32)

  • Index Cond: ((company = 81) AND ((depot)::text = 81::text) AND ((part)::text = (stock.part)::text))
19. 0.160 1.056 ↑ 1.0 1 32

Aggregate (cost=13.37..13.38 rows=1 width=8) (actual time=0.033..0.033 rows=1 loops=32)

20. 0.896 0.896 ↑ 2.0 1 32

Index Scan using stock_idx1 on stock a (cost=0.00..13.36 rows=2 width=8) (actual time=0.027..0.028 rows=1 loops=32)

  • Index Cond: ((company = 81) AND ((depot)::text = 81::text) AND ((part)::text = (stock.part)::text))
21. 0.160 1.056 ↑ 1.0 1 32

Aggregate (cost=13.37..13.38 rows=1 width=8) (actual time=0.033..0.033 rows=1 loops=32)

22. 0.896 0.896 ↑ 2.0 1 32

Index Scan using stock_idx1 on stock a (cost=0.00..13.36 rows=2 width=8) (actual time=0.027..0.028 rows=1 loops=32)

  • Index Cond: ((company = 81) AND ((depot)::text = 81::text) AND ((part)::text = (stock.part)::text))
23. 0.160 1.056 ↑ 1.0 1 32

Aggregate (cost=13.37..13.38 rows=1 width=8) (actual time=0.033..0.033 rows=1 loops=32)

24. 0.896 0.896 ↑ 2.0 1 32

Index Scan using stock_idx1 on stock a (cost=0.00..13.36 rows=2 width=8) (actual time=0.027..0.028 rows=1 loops=32)

  • Index Cond: ((company = 81) AND ((depot)::text = 81::text) AND ((part)::text = (stock.part)::text))
25. 0.224 1.120 ↑ 1.0 1 32

Aggregate (cost=13.37..13.38 rows=1 width=8) (actual time=0.035..0.035 rows=1 loops=32)

26. 0.896 0.896 ↑ 2.0 1 32

Index Scan using stock_idx1 on stock a (cost=0.00..13.36 rows=2 width=8) (actual time=0.027..0.028 rows=1 loops=32)

  • Index Cond: ((company = 81) AND ((depot)::text = 81::text) AND ((part)::text = (stock.part)::text))
27. 0.256 1.152 ↑ 1.0 1 32

Aggregate (cost=13.37..13.38 rows=1 width=8) (actual time=0.036..0.036 rows=1 loops=32)

28. 0.896 0.896 ↑ 2.0 1 32

Index Scan using stock_idx1 on stock a (cost=0.00..13.36 rows=2 width=8) (actual time=0.027..0.028 rows=1 loops=32)

  • Index Cond: ((company = 81) AND ((depot)::text = 81::text) AND ((part)::text = (stock.part)::text))
29. 0.160 1.056 ↑ 1.0 1 32

Aggregate (cost=13.37..13.38 rows=1 width=8) (actual time=0.033..0.033 rows=1 loops=32)

30. 0.896 0.896 ↑ 2.0 1 32

Index Scan using stock_idx1 on stock a (cost=0.00..13.36 rows=2 width=8) (actual time=0.027..0.028 rows=1 loops=32)

  • Index Cond: ((company = 81) AND ((depot)::text = 81::text) AND ((part)::text = (stock.part)::text))