explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9meXl

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 3,911.773 ↓ 1.1 200 1

Limit (cost=103,469.59..103,470.07 rows=189 width=29) (actual time=3,911.750..3,911.773 rows=200 loops=1)

2. 616.610 3,911.756 ↓ 1.1 200 1

Sort (cost=103,469.59..103,470.07 rows=189 width=29) (actual time=3,911.746..3,911.756 rows=200 loops=1)

  • Sort Key: sq_batch.priority, (md5(((sq_batchaimmark.batch_id + 2425))::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. 281.816 3,295.146 ↓ 1,548.2 292,614 1

Nested Loop Anti Join (cost=226.78..103,462.45 rows=189 width=29) (actual time=20.525..3,295.146 rows=292,614 loops=1)

4. 119.987 2,120.746 ↓ 1,533.6 297,528 1

Nested Loop (cost=226.06..91,680.57 rows=194 width=29) (actual time=5.095..2,120.746 rows=297,528 loops=1)

5. 231.434 1,405.577 ↓ 1,503.0 297,591 1

Nested Loop (cost=208.68..86,664.71 rows=198 width=33) (actual time=5.063..1,405.577 rows=297,591 loops=1)

6. 86.612 194.415 ↓ 68.4 489,864 1

Nested Loop (cost=208.24..26,542.39 rows=7,158 width=12) (actual time=5.040..194.415 rows=489,864 loops=1)

7. 0.295 5.813 ↓ 5.9 47 1

Hash Join (cost=207.81..1,119.25 rows=8 width=10) (actual time=5.030..5.813 rows=47 loops=1)

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

Seq Scan on sq_batch (cost=0.00..906.52 rows=968 width=6) (actual time=0.061..5.172 rows=459 loops=1)

  • Filter: ((priority > 0) AND (status = 0))
  • Rows Removed by Filter: 27858
9. 0.083 0.346 ↓ 3.0 702 1

Hash (cost=204.84..204.84 rows=237 width=4) (actual time=0.346..0.346 rows=702 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
10. 0.195 0.263 ↓ 3.0 702 1

Bitmap Heap Scan on sq_batch_marktypes (cost=23.26..204.84 rows=237 width=4) (actual time=0.076..0.263 rows=702 loops=1)

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

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

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

Index Scan using sq_batchaimmark_batch_id on sq_batchaimmark (cost=0.44..3,127.33 rows=5,056 width=10) (actual time=0.010..2.170 rows=10,423 loops=47)

  • Index Cond: (batch_id = sq_batch.id)
13. 979.728 979.728 ↑ 1.0 1 489,864

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=489,864)

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

Index Scan using sq_aim_pkey on sq_aim (cost=17.38..25.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=297,591)

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

SubPlan (forIndex Scan)

16. 0.003 0.015 ↑ 1.0 2 1

Nested Loop (cost=0.56..16.94 rows=2 width=4) (actual time=0.012..0.015 rows=2 loops=1)

17. 0.006 0.006 ↑ 1.0 2 1

Index Only Scan using sq_userregion_user_id_131c777209eff276_uniq on sq_userregion u1_1 (cost=0.28..8.31 rows=2 width=4) (actual time=0.006..0.006 rows=2 loops=1)

  • Index Cond: (user_id = 39)
  • Heap Fetches: 0
18. 0.006 0.006 ↑ 1.0 1 2

Index Only Scan using sq_region_pkey on sq_region u0 (cost=0.28..4.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2)

  • Index Cond: (id = u1_1.region_id)
  • Heap Fetches: 0
19. 0.000 892.584 ↓ 0.0 0 297,528

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

20. 595.056 595.056 ↑ 6.0 1 297,528

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=297,528)

  • Index Cond: (aimmark_id = sq_aimmark.id)
21. 303.263 303.263 ↓ 0.0 0 303,263

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=303,263)

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