explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eWO00 : Postgres Issue

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 12,488.594 ↑ 2.0 1 1

HashAggregate (cost=4,354,075.43..4,354,075.45 rows=2 width=45) (actual time=12,488.594..12,488.594 rows=1 loops=1)

  • Group Key: p.product_short_code, p.short_name
2.          

CTE q

3. 0.025 11,440.422 ↑ 1,136,378.7 6 1

Recursive Union (cost=2.83..3,971,849.25 rows=6,818,272 width=49) (actual time=0.095..11,440.422 rows=6 loops=1)

4. 0.018 0.117 ↑ 53.0 4 1

Hash Join (cost=2.83..730.01 rows=212 width=49) (actual time=0.093..0.117 rows=4 loops=1)

  • Hash Cond: ((u.category_code)::text = (c.category_code)::text)
5. 0.077 0.077 ↑ 53.0 4 1

Index Scan using ind_users2 on users u (cost=0.43..726.91 rows=212 width=49) (actual time=0.059..0.077 rows=4 loops=1)

  • Index Cond: ((parent_id)::text = 'COD0002453525'::text)
  • Filter: (((user_id)::text <> 'COD0002453525'::text) AND ((status)::text <> ALL ('{N,C}'::text[])))
6. 0.007 0.022 ↑ 1.0 18 1

Hash (cost=2.18..2.18 rows=18 width=4) (actual time=0.022..0.022 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
7. 0.015 0.015 ↑ 1.0 18 1

Seq Scan on categories c (cost=0.00..2.18 rows=18 width=4) (actual time=0.011..0.015 rows=18 loops=1)

8. 510.218 11,440.280 ↑ 681,806.0 1 2

Merge Join (cost=366,784.65..383,475.38 rows=681,806 width=49) (actual time=5,720.135..5,720.140 rows=1 loops=2)

  • Merge Cond: ((q_1.user_id)::text = (u1.parent_id)::text)
9. 0.064 0.068 ↑ 706.7 3 2

Sort (cost=159.53..164.83 rows=2,120 width=48) (actual time=0.032..0.034 rows=3 loops=2)

  • Sort Key: q_1.user_id
  • Sort Method: quicksort Memory: 25kB
10. 0.004 0.004 ↑ 706.7 3 2

WorkTable Scan on q q_1 (cost=0.00..42.40 rows=2,120 width=48) (actual time=0.002..0.002 rows=3 loops=2)

11. 10,285.569 10,929.994 ↑ 1.6 1,263,036 2

Sort (cost=366,625.12..371,556.16 rows=1,972,414 width=49) (actual time=5,227.590..5,464.997 rows=1,263,036 loops=2)

  • Sort Key: u1.parent_id
  • Sort Method: quicksort Memory: 324,020kB
12. 644.425 644.425 ↓ 1.0 2,001,336 1

Seq Scan on users u1 (cost=0.00..160,394.14 rows=1,972,414 width=49) (actual time=0.010..644.425 rows=2,001,336 loops=1)

13. 8.102 12,488.553 ↑ 2,272,757.3 3 1

Hash Join (cost=92,449.61..331,089.13 rows=6,818,272 width=20) (actual time=1,048.186..12,488.553 rows=3 loops=1)

  • Hash Cond: ((q.user_id)::text = (ub.user_id)::text)
14. 11,440.439 11,440.439 ↑ 1,136,378.7 6 1

CTE Scan on q (cost=0.00..136,365.44 rows=6,818,272 width=48) (actual time=0.097..11,440.439 rows=6 loops=1)

15. 477.841 1,040.012 ↑ 1.0 1,533,581 1

Hash (cost=73,192.76..73,192.76 rows=1,540,548 width=34) (actual time=1,040.012..1,040.012 rows=1,533,581 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 113,730kB
16. 433.188 562.171 ↑ 1.0 1,533,581 1

Hash Join (cost=1.04..73,192.76 rows=1,540,548 width=34) (actual time=0.069..562.171 rows=1,533,581 loops=1)

  • Hash Cond: ((ub.product_code)::text = (p.product_code)::text)
17. 128.967 128.967 ↑ 1.0 1,533,581 1

Seq Scan on user_balances ub (cost=0.00..60,578.48 rows=1,540,548 width=28) (actual time=0.031..128.967 rows=1,533,581 loops=1)

18. 0.005 0.016 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=20) (actual time=0.016..0.016 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.011 0.011 ↑ 1.0 2 1

Seq Scan on products p (cost=0.00..1.02 rows=2 width=20) (actual time=0.009..0.011 rows=2 loops=1)

Planning time : 1.153 ms
Execution time : 12,538.235 ms