explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AzSo

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 4,158.133 ↓ 1.1 200 1

Limit (cost=102,500.22..102,500.70 rows=189 width=29) (actual time=4,158.111..4,158.133 rows=200 loops=1)

2. 869.852 4,158.116 ↓ 1.1 200 1

Sort (cost=102,500.22..102,500.70 rows=189 width=29) (actual time=4,158.107..4,158.116 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. 281.312 3,288.264 ↓ 1,496.8 282,902 1

Nested Loop Anti Join (cost=222.45..102,493.08 rows=189 width=29) (actual time=20.057..3,288.264 rows=282,902 loops=1)

4. 96.476 2,143.582 ↓ 1,483.5 287,790 1

Nested Loop (cost=221.73..90,711.31 rows=194 width=29) (actual time=5.110..2,143.582 rows=287,790 loops=1)

5. 252.507 1,471.348 ↓ 1,453.9 287,879 1

Nested Loop (cost=208.68..86,553.78 rows=198 width=33) (actual time=5.093..1,471.348 rows=287,879 loops=1)

6. 88.733 198.213 ↓ 71.4 510,314 1

Nested Loop (cost=208.24..26,507.68 rows=7,149 width=12) (actual time=5.069..198.213 rows=510,314 loops=1)

7. 0.153 5.698 ↓ 6.1 49 1

Hash Join (cost=207.81..1,117.35 rows=8 width=10) (actual time=5.058..5.698 rows=49 loops=1)

  • Hash Cond: (sq_batch.id = sq_batch_marktypes.batch_id)
8. 5.201 5.201 ↑ 2.0 473 1

Seq Scan on sq_batch (cost=0.00..904.63 rows=966 width=6) (actual time=0.061..5.201 rows=473 loops=1)

  • Filter: ((priority > 0) AND (status = 0))
  • Rows Removed by Filter: 27842
9. 0.099 0.344 ↓ 3.0 701 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
10. 0.181 0.245 ↓ 3.0 701 1

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

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

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

  • Index Cond: (marktype_id = ANY ('{52,57,49,53,45}'::integer[]))
12. 103.782 103.782 ↓ 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.118 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. 575.754 575.758 ↑ 1.0 1 287,879

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=287,879)

  • 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.000 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.004 0.004 ↓ 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.004..0.004 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 863.370 ↓ 0.0 0 287,790

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

20. 575.580 575.580 ↑ 6.0 1 287,790

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=287,790)

  • Index Cond: (aimmark_id = sq_aimmark.id)
21. 300.529 300.529 ↓ 0.0 0 300,529

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=300,529)

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