explain.depesz.com

A tool for finding a real cause for slow queries.

Result: IlGO

options
# exclusive inclusive rows x rows loops node
1. 0.315 16842.391 ↑ 2594.6 32 1
→

Sort (cost=8141882.12..8142089.69 rows=83028 width=181) (actual time=16842.383..16842.391 rows=32 loops=1)

  • Sort Key: stock.part, stock.company, stock.depot
  • Sort Method: quicksort Memory: 39kB
2. 2807.663 16842.076 ↑ 2594.6 32 1
→

Hash Join (cost=120017.72..8135098.19 rows=83028 width=181) (actual time=10712.434..16842.076 rows=32 loops=1)

  • Hash Cond: (stprice.stockid = stock.stockid)
3. 3337.813 3337.813 ↑ 1.0 5608223 1
→

Seq Scan on stprice (cost=0.00..203621.42 rows=5608242 width=13) (actual time=0.013..3337.813 rows=5608223 loops=1)

4. 0.279 10688.696 ↑ 2594.6 32 1
→

Hash (cost=118979.87..118979.87 rows=83028 width=172) (actual time=10688.696..10688.696 rows=32 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 8kB
5. 227.401 10688.417 ↑ 2594.6 32 1
→

Merge Left Join (cost=112411.09..118979.87 rows=83028 width=172) (actual time=10680.599..10688.417 rows=32 loops=1)

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

Sort (cost=10525.60..10733.13 rows=83014 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 ↑ 2594.2 32 1
→

Nested Loop (cost=23.34..3742.91 rows=83014 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=1000 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. 8594.172 10024.853 ↑ 2.1 328042 1
→

Sort (cost=101778.57..103535.01 rows=702575 width=30) (actual time=9746.386..10024.853 rows=328042 loops=1)

  • Sort Key: grpdesc.id, grpdesc.company
  • Sort Method: external sort Disk: 32200kB
15. 1430.681 1430.681 ↓ 1.0 703420 1
→

Seq Scan on grpdesc (cost=0.00..33550.49 rows=702575 width=30) (actual time=0.042..1430.681 rows=703420 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))