explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8B9e

Settings
# exclusive inclusive rows x rows loops node
1. 1.306 4,747.572 ↑ 1.0 1 1

Aggregate (cost=936,814.46..936,814.47 rows=1 width=4) (actual time=4,747.572..4,747.572 rows=1 loops=1)

2. 9.938 4,746.266 ↓ 5.2 13,119 1

Nested Loop Anti Join (cost=223,336.36..936,808.14 rows=2,528 width=4) (actual time=2,565.377..4,746.266 rows=13,119 loops=1)

3. 2.492 4,696.971 ↓ 5.1 13,119 1

Nested Loop (cost=223,335.64..729,480.42 rows=2,583 width=4) (actual time=2,565.354..4,696.971 rows=13,119 loops=1)

4. 324.402 4,668.241 ↓ 5.0 13,119 1

Hash Join (cost=223,309.26..638,760.20 rows=2,645 width=8) (actual time=2,565.278..4,668.241 rows=13,119 loops=1)

  • Hash Cond: (sq_batchaimmark.aimmark_id = sq_aimmark.id)
5. 2,008.485 4,017.184 ↓ 2.9 1,442,339 1

Hash Join (cost=781.12..409,429.57 rows=492,803 width=4) (actual time=4.619..4,017.184 rows=1,442,339 loops=1)

  • Hash Cond: (sq_batchaimmark.batch_id = sq_batch.id)
6. 2,004.139 2,004.139 ↓ 1.0 19,777,206 1

Seq Scan on sq_batchaimmark (cost=0.00..332,605.03 rows=18,964,103 width=8) (actual time=0.007..2,004.139 rows=19,777,206 loops=1)

7. 0.037 4.560 ↑ 3.4 199 1

Hash (cost=772.75..772.75 rows=670 width=4) (actual time=4.560..4.560 rows=199 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
8. 4.523 4.523 ↑ 3.4 199 1

Seq Scan on sq_batch (cost=0.00..772.75 rows=670 width=4) (actual time=0.055..4.523 rows=199 loops=1)

  • Filter: ((priority > 0) AND (status = 0))
  • Rows Removed by Filter: 25577
9. 5.652 326.655 ↑ 2.2 32,517 1

Hash (cost=221,649.43..221,649.43 rows=70,297 width=8) (actual time=326.655..326.655 rows=32,517 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1271kB
10. 81.180 321.003 ↑ 2.2 32,517 1

Bitmap Heap Scan on sq_aimmark (cost=74,525.49..221,649.43 rows=70,297 width=8) (actual time=269.334..321.003 rows=32,517 loops=1)

  • Recheck Cond: ((marktype_id = 49) AND (status = ANY ('{2,3}'::integer[])))
  • Filter: (NOT is_locked)
  • Rows Removed by Filter: 105
11. 17.451 239.823 ↓ 0.0 0 1

BitmapAnd (cost=74,525.49..74,525.49 rows=70,297 width=0) (actual time=239.823..239.823 rows=0 loops=1)

12. 87.152 87.152 ↓ 1.3 844,174 1

Bitmap Index Scan on sq_aimmark_marktype_id (cost=0.00..28,857.04 rows=644,331 width=0) (actual time=87.152..87.152 rows=844,174 loops=1)

  • Index Cond: (marktype_id = 49)
13. 135.220 135.220 ↓ 1.1 1,546,976 1

Bitmap Index Scan on idx_sq_aimmark_status (cost=0.00..45,633.05 rows=1,428,791 width=0) (actual time=135.220..135.220 rows=1,546,976 loops=1)

  • Index Cond: (status = ANY ('{2,3}'::integer[]))
14. 26.212 26.238 ↑ 1.0 1 13,119

Index Scan using sq_aim_pkey on sq_aim (cost=26.37..34.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=13,119)

  • Index Cond: (id = sq_aimmark.aim_id)
  • Filter: ((hashed SubPlan 1) OR (country_id = 28) OR (region_id IS NULL))
15.          

SubPlan (forIndex Scan)

16. 0.004 0.026 ↑ 1.0 5 1

Nested Loop (cost=0.56..25.93 rows=5 width=4) (actual time=0.016..0.026 rows=5 loops=1)

17. 0.007 0.007 ↑ 1.0 5 1

Index Only Scan using sq_userregion_user_id_131c777209eff276_uniq on sq_userregion u1_1 (cost=0.28..4.36 rows=5 width=4) (actual time=0.007..0.007 rows=5 loops=1)

  • Index Cond: (user_id = 7)
  • Heap Fetches: 0
18. 0.015 0.015 ↑ 1.0 1 5

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=5)

  • Index Cond: (id = u1_1.region_id)
  • Heap Fetches: 0
19. 0.000 39.357 ↓ 0.0 0 13,119

Nested Loop (cost=0.72..80.26 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=13,119)

20. 26.238 26.238 ↑ 8.0 1 13,119

Index Scan using sq_batchaimmark_aimmark_id on sq_batchaimmark u1 (cost=0.44..15.09 rows=8 width=8) (actual time=0.001..0.002 rows=1 loops=13,119)

  • Index Cond: (aimmark_id = sq_aimmark.id)
21. 16.370 16.370 ↓ 0.0 0 16,370

Index Scan using sq_batch_pkey on sq_batch u2 (cost=0.29..8.14 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=16,370)

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