explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UUDs

Settings
# exclusive inclusive rows x rows loops node
1. 0.125 47.766 ↑ 1.4 153 1

HashAggregate (cost=1,756.91..1,759.01 rows=210 width=20) (actual time=47.722..47.766 rows=153 loops=1)

  • Group Key: user_controlled.activation, user_controlled.user_id, user_controlled.active_orders
2.          

CTE user_bag

3. 10.970 44.699 ↓ 1.1 2,142 1

Hash Join (cost=18.27..1,645.29 rows=1,950 width=36) (actual time=8.467..44.699 rows=2,142 loops=1)

  • Hash Cond: (ob.user_id = ur.user_id)
  • Join Filter: (ob.viewed_at > ur.created_at)
  • Rows Removed by Join Filter: 2641
4. 32.947 32.947 ↓ 1.5 35,817 1

Index Scan using order_bag_created_at_index on order_bag ob (cost=0.43..1,461.81 rows=24,673 width=20) (actual time=0.116..32.947 rows=35,817 loops=1)

  • Index Cond: (created_at > (now() - '06:00:00'::interval))
5. 0.218 0.782 ↓ 1.0 252 1

Hash (cost=14.73..14.73 rows=249 width=20) (actual time=0.782..0.782 rows=252 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
6. 0.239 0.564 ↓ 1.0 252 1

Hash Join (cost=4.81..14.73 rows=249 width=20) (actual time=0.187..0.564 rows=252 loops=1)

  • Hash Cond: (ur.role_id = r.id)
7. 0.165 0.165 ↓ 1.0 252 1

Seq Scan on user_roles ur (cost=0.00..6.49 rows=249 width=16) (actual time=0.010..0.165 rows=252 loops=1)

8. 0.073 0.160 ↓ 1.0 128 1

Hash (cost=3.25..3.25 rows=125 width=8) (actual time=0.160..0.160 rows=128 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
9. 0.087 0.087 ↓ 1.0 128 1

Seq Scan on roles r (cost=0.00..3.25 rows=125 width=8) (actual time=0.011..0.087 rows=128 loops=1)

10.          

CTE user_not_controlled

11. 0.127 37.730 ↓ 14.2 142 1

GroupAggregate (cost=39.17..39.37 rows=10 width=20) (actual time=37.546..37.730 rows=142 loops=1)

  • Group Key: user_bag.user_id
12. 0.208 37.603 ↓ 26.5 265 1

Sort (cost=39.17..39.19 rows=10 width=12) (actual time=37.538..37.603 rows=265 loops=1)

  • Sort Key: user_bag.user_id
  • Sort Method: quicksort Memory: 37kB
13. 37.395 37.395 ↓ 26.5 265 1

CTE Scan on user_bag (cost=0.00..39.00 rows=10 width=12) (actual time=0.974..37.395 rows=265 loops=1)

  • Filter: ((viewed_at IS NOT NULL) AND (controlled_at IS NULL))
  • Rows Removed by Filter: 1877
14.          

CTE user_controlled

15. 0.035 47.536 ↑ 18.2 11 1

HashAggregate (cost=62.38..64.38 rows=200 width=20) (actual time=47.532..47.536 rows=11 loops=1)

  • Group Key: user_bag_1.user_id
16. 0.578 47.501 ↑ 14.0 69 1

Hash Anti Join (cost=0.33..55.13 rows=966 width=12) (actual time=46.382..47.501 rows=69 loops=1)

  • Hash Cond: (user_bag_1.user_id = user_not_controlled_1.user_id)
17. 9.042 9.042 ↑ 1.0 1,877 1

CTE Scan on user_bag user_bag_1 (cost=0.00..39.00 rows=1,931 width=12) (actual time=8.470..9.042 rows=1,877 loops=1)

  • Filter: ((viewed_at IS NOT NULL) AND (controlled_at IS NOT NULL))
  • Rows Removed by Filter: 265
18. 0.064 37.881 ↓ 14.2 142 1

Hash (cost=0.20..0.20 rows=10 width=4) (actual time=37.881..37.881 rows=142 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
19. 37.817 37.817 ↓ 14.2 142 1

CTE Scan on user_not_controlled user_not_controlled_1 (cost=0.00..0.20 rows=10 width=4) (actual time=37.548..37.817 rows=142 loops=1)

20. 0.079 47.641 ↑ 1.4 153 1

Append (cost=0.00..6.30 rows=210 width=20) (actual time=47.535..47.641 rows=153 loops=1)

21. 47.542 47.542 ↑ 18.2 11 1

CTE Scan on user_controlled (cost=0.00..4.00 rows=200 width=20) (actual time=47.534..47.542 rows=11 loops=1)

22. 0.020 0.020 ↓ 14.2 142 1

CTE Scan on user_not_controlled (cost=0.00..0.20 rows=10 width=20) (actual time=0.002..0.020 rows=142 loops=1)

Planning time : 2.252 ms
Execution time : 48.099 ms