explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Afq2

Settings
# exclusive inclusive rows x rows loops node
1. 227.359 227.359 ↑ 4.0 147 1

CTE Scan on user_bag (cost=31,631.61..31,670.83 rows=581 width=32) (actual time=227.145..227.359 rows=147 loops=1)

  • Filter: (active_orders < max_orders)
  • Rows Removed by Filter: 103
2.          

CTE user_bag

3. 0.211 227.201 ↑ 7.0 250 1

Sort (cost=31,627.25..31,631.61 rows=1,743 width=36) (actual time=227.138..227.201 rows=250 loops=1)

  • Sort Key: (sum(CASE WHEN (ob.viewed_at IS NOT NULL) THEN 1 ELSE 0 END)), (COALESCE(min(ob.viewed_at), min(ur.created_at))), (max(ob2.controlled_at))
  • Sort Method: quicksort Memory: 44kB
4. 2.121 226.990 ↑ 7.0 250 1

GroupAggregate (cost=31,480.00..31,533.41 rows=1,743 width=36) (actual time=223.955..226.990 rows=250 loops=1)

  • Group Key: ur.user_id, r.max_orders, ur.role_id
5. 3.103 224.869 ↓ 2.4 4,306 1

Sort (cost=31,480.00..31,484.50 rows=1,799 width=36) (actual time=223.944..224.869 rows=4,306 loops=1)

  • Sort Key: ur.user_id, r.max_orders, ur.role_id
  • Sort Method: quicksort Memory: 529kB
6. 2.976 221.766 ↓ 2.4 4,306 1

Nested Loop Left Join (cost=15.27..31,382.74 rows=1,799 width=36) (actual time=3.647..221.766 rows=4,306 loops=1)

  • Join Filter: (ob.created_at > (now() - '06:00:00'::interval))
  • Rows Removed by Join Filter: 243
7. 0.382 27.354 ↓ 1.5 371 1

Hash Join (cost=14.85..1,491.85 rows=249 width=36) (actual time=3.627..27.354 rows=371 loops=1)

  • Hash Cond: (ur.role_id = r.id)
8. 4.925 26.889 ↓ 1.5 371 1

Hash Right Join (cost=10.03..1,483.61 rows=249 width=32) (actual time=3.534..26.889 rows=371 loops=1)

  • Hash Cond: (ob.user_id = ur.user_id)
  • Join Filter: (ob.viewed_at > ur.created_at)
  • Rows Removed by Join Filter: 1
9. 21.812 21.812 ↓ 11.7 22,151 1

Index Scan using order_bag_created_at_index on order_bag ob (cost=0.43..1,461.35 rows=1,886 width=20) (actual time=0.023..21.812 rows=22,151 loops=1)

  • Index Cond: (created_at > (now() - '06:00:00'::interval))
  • Filter: (controlled_at IS NULL)
  • Rows Removed by Filter: 13528
10. 0.082 0.152 ↓ 1.0 250 1

Hash (cost=6.49..6.49 rows=249 width=16) (actual time=0.152..0.152 rows=250 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
11. 0.070 0.070 ↓ 1.0 250 1

Seq Scan on user_roles ur (cost=0.00..6.49 rows=249 width=16) (actual time=0.005..0.070 rows=250 loops=1)

12. 0.040 0.083 ↓ 1.0 128 1

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

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

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

14. 191.436 191.436 ↑ 20.0 12 371

Index Scan using order_bag_user_id_index on order_bag ob2 (cost=0.42..115.84 rows=240 width=20) (actual time=0.061..0.516 rows=12 loops=371)

  • Index Cond: (ur.user_id = user_id)
  • Filter: ((controlled_at IS NOT NULL) AND (viewed_at > ur.created_at))
  • Rows Removed by Filter: 670
Planning time : 0.615 ms
Execution time : 227.510 ms