explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qSoM

Settings
# exclusive inclusive rows x rows loops node
1. 0.019 3,736.199 ↓ 1.1 200 1

Limit (cost=102,502.23..102,502.70 rows=189 width=29) (actual time=3,736.175..3,736.199 rows=200 loops=1)

2. 413.953 3,736.180 ↓ 1.1 200 1

Sort (cost=102,502.23..102,502.70 rows=189 width=29) (actual time=3,736.171..3,736.180 rows=200 loops=1)

  • Sort Key: sq_batch.priority, (md5(((sq_batchaimmark.batch_id + 1966))::text)), sq_aimmark.ancestor_id, sq_aimmark.status, sq_batchaimmark.descendants_complete_cnt, sq_aimmark.avg_user_skill, sq_aimmar
  • Sort Method: top-N heapsort Memory: 53kB
3. 280.072 3,322.227 ↓ 1,486.3 280,909 1

Nested Loop Anti Join (cost=222.45..102,495.08 rows=189 width=29) (actual time=19.614..3,322.227 rows=280,909 loops=1)

4. 90.773 2,184.764 ↓ 1,473.2 285,797 1

Nested Loop (cost=221.73..90,713.22 rows=194 width=29) (actual time=4.975..2,184.764 rows=285,797 loops=1)

5. 304.019 1,522.219 ↓ 1,443.9 285,886 1

Nested Loop (cost=208.68..86,555.69 rows=198 width=33) (actual time=4.960..1,522.219 rows=285,886 loops=1)

6. 88.539 197.572 ↓ 71.4 510,314 1

Nested Loop (cost=208.24..26,509.58 rows=7,149 width=12) (actual time=4.936..197.572 rows=510,314 loops=1)

7. 0.137 5.545 ↓ 6.1 49 1

Hash Join (cost=207.81..1,119.25 rows=8 width=10) (actual time=4.924..5.545 rows=49 loops=1)

  • Hash Cond: (sq_batch.id = sq_batch_marktypes.batch_id)
8. 5.049 5.049 ↑ 2.1 462 1

Seq Scan on sq_batch (cost=0.00..906.52 rows=968 width=6) (actual time=0.056..5.049 rows=462 loops=1)

  • Filter: ((priority > 0) AND (status = 0))
  • Rows Removed by Filter: 27853
9. 0.098 0.359 ↓ 3.0 701 1

Hash (cost=204.84..204.84 rows=237 width=4) (actual time=0.359..0.359 rows=701 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
10. 0.194 0.261 ↓ 3.0 701 1

Bitmap Heap Scan on sq_batch_marktypes (cost=23.26..204.84 rows=237 width=4) (actual time=0.074..0.261 rows=701 loops=1)

  • Recheck Cond: (marktype_id = ANY ('{52,57,49,53,45}'::integer[]))
11. 0.067 0.067 ↓ 3.0 701 1

Bitmap Index Scan on sq_batch_marktypes_061cf1f9 (cost=0.00..23.20 rows=237 width=0) (actual time=0.067..0.067 rows=701 loops=1)

  • Index Cond: (marktype_id = ANY ('{52,57,49,53,45}'::integer[]))
12. 103.488 103.488 ↓ 2.1 10,415 49

Index Scan using sq_batchaimmark_batch_id on sq_batchaimmark (cost=0.44..3,123.30 rows=5,049 width=10) (actual time=0.009..2.112 rows=10,415 loops=49)

  • Index Cond: (batch_id = sq_batch.id)
13. 1,020.628 1,020.628 ↑ 1.0 1 510,314

Index Scan using sq_aimmark_pkey on sq_aimmark (cost=0.44..8.39 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=510,314)

  • Index Cond: (id = sq_batchaimmark.aimmark_id)
  • Filter: ((NOT tutorial) AND (NOT is_locked) AND ((status = 2) OR (status = 3)) AND (marktype_id = ANY ('{52,57,49,53,45}'::integer[])))
  • Rows Removed by Filter: 0
14. 571.768 571.772 ↑ 1.0 1 285,886

Index Scan using sq_aim_pkey on sq_aim (cost=13.05..20.99 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=285,886)

  • Index Cond: (id = sq_aimmark.aim_id)
  • Filter: ((hashed SubPlan 1) OR (region_id IS NULL))
  • Rows Removed by Filter: 0
15.          

SubPlan (forIndex Scan)

16. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.56..12.61 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=1)

17. 0.003 0.003 ↓ 0.0 0 1

Index Only Scan using sq_userregion_user_id_131c777209eff276_uniq on sq_userregion u1_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Index Cond: (user_id = 846)
  • Heap Fetches: 0
18. 0.000 0.000 ↓ 0.0 0

Index Only Scan using sq_region_pkey on sq_region u0 (cost=0.28..4.30 rows=1 width=4) (never executed)

  • Index Cond: (id = u1_1.region_id)
  • Heap Fetches: 0
19. 0.000 857.391 ↓ 0.0 0 285,797

Nested Loop (cost=0.72..60.71 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=285,797)

20. 571.594 571.594 ↑ 6.0 1 285,797

Index Scan using sq_batchaimmark_aimmark_id on sq_batchaimmark u1 (cost=0.44..11.36 rows=6 width=8) (actual time=0.001..0.002 rows=1 loops=285,797)

  • Index Cond: (aimmark_id = sq_aimmark.id)
21. 297.435 297.435 ↓ 0.0 0 297,435

Index Scan using sq_batch_pkey on sq_batch u2 (cost=0.29..8.22 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=297,435)

  • Index Cond: (id = u1.batch_id)
  • Filter: only_for_assigned_users
  • Rows Removed by Filter: 1