explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yxn8

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 180,193.751 ↓ 1.1 200 1

Limit (cost=102,296.01..102,296.48 rows=189 width=29) (actual time=180,193.728..180,193.751 rows=200 loops=1)

2. 940.760 180,193.731 ↓ 1.1 200 1

Sort (cost=102,296.01..102,296.48 rows=189 width=29) (actual time=180,193.724..180,193.731 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. 317.144 179,252.971 ↓ 1,434.3 271,085 1

Nested Loop Anti Join (cost=222.45..102,288.86 rows=189 width=29) (actual time=2,812.957..179,252.971 rows=271,085 loops=1)

4. 76.235 174,516.659 ↓ 1,431.1 276,198 1

Nested Loop (cost=221.73..90,567.85 rows=193 width=29) (actual time=101.975..174,516.659 rows=276,198 loops=1)

5. 366.687 22,482.574 ↓ 1,395.4 276,287 1

Nested Loop (cost=208.68..86,410.35 rows=198 width=33) (actual time=70.550..22,482.574 rows=276,287 loops=1)

6. 87.567 10,844.001 ↓ 68.7 490,082 1

Nested Loop (cost=208.24..26,465.42 rows=7,137 width=12) (actual time=50.714..10,844.001 rows=490,082 loops=1)

7. 0.187 38.178 ↓ 6.0 48 1

Hash Join (cost=207.81..1,117.35 rows=8 width=10) (actual time=37.414..38.178 rows=48 loops=1)

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

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

  • Filter: ((priority > 0) AND (status = 0))
  • Rows Removed by Filter: 27842
9. 0.094 32.717 ↓ 3.0 700 1

Hash (cost=204.84..204.84 rows=237 width=4) (actual time=32.717..32.717 rows=700 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
10. 0.175 32.623 ↓ 3.0 700 1

Bitmap Heap Scan on sq_batch_marktypes (cost=23.26..204.84 rows=237 width=4) (actual time=32.457..32.623 rows=700 loops=1)

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

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

  • Index Cond: (marktype_id = ANY ('{52,57,49,53,45}'::integer[]))
12. 10,718.256 10,718.256 ↓ 2.0 10,210 48

Index Scan using sq_batchaimmark_batch_id on sq_batchaimmark (cost=0.44..3,118.11 rows=5,040 width=10) (actual time=4.559..223.297 rows=10,210 loops=48)

  • Index Cond: (batch_id = sq_batch.id)
13. 11,271.886 11,271.886 ↑ 1.0 1 490,082

Index Scan using sq_aimmark_pkey on sq_aimmark (cost=0.44..8.39 rows=1 width=25) (actual time=0.023..0.023 rows=1 loops=490,082)

  • 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. 151,940.366 151,957.850 ↑ 1.0 1 276,287

Index Scan using sq_aim_pkey on sq_aim (cost=13.05..20.99 rows=1 width=4) (actual time=0.549..0.550 rows=1 loops=276,287)

  • 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 17.484 ↓ 0.0 0 1

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

17. 17.483 17.483 ↓ 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=17.483..17.483 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. 256.003 4,419.168 ↓ 0.0 0 276,198

Nested Loop (cost=0.72..60.71 rows=1 width=4) (actual time=0.016..0.016 rows=0 loops=276,198)

20. 3,866.772 3,866.772 ↑ 6.0 1 276,198

Index Scan using sq_batchaimmark_aimmark_id on sq_batchaimmark u1 (cost=0.44..11.36 rows=6 width=8) (actual time=0.014..0.014 rows=1 loops=276,198)

  • Index Cond: (aimmark_id = sq_aimmark.id)
21. 296.393 296.393 ↓ 0.0 0 296,393

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=296,393)

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