explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NPOn : Optimization for: plan #w4z

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 89.339 2,634.020 ↑ 1.0 229,559 1

Gather (cost=287,091.78..311,120.71 rows=229,559 width=13) (actual time=2,097.578..2,634.020 rows=229,559 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
2. 216.266 2,544.681 ↑ 1.3 57,390 4

Merge Left Join (cost=286,091.78..287,164.81 rows=74,051 width=13) (actual time=2,094.293..2,544.681 rows=57,390 loops=4)

  • Merge Cond: (m.media = ((('{'::text || shv.tag) || '}'::text)))
  • Join Filter: ((m.media ~~ '{%'::text) AND (shv.parent_type = m.parent_type) AND (shv.parent_id = m.parent_id))
  • Rows Removed by Join Filter: 1045220
3. 162.346 2,262.320 ↑ 1.3 57,390 4

Merge Left Join (cost=285,700.33..286,636.08 rows=74,051 width=32) (actual time=2,075.251..2,262.320 rows=57,390 loops=4)

  • Merge Cond: (m.media = ((me.id)::text))
  • Join Filter: (m.media ~ '^[0-9\.]+$'::text)
4. 167.645 410.413 ↑ 1.3 57,390 4

Sort (cost=228,223.81..228,260.84 rows=74,051 width=28) (actual time=406.972..410.413 rows=57,390 loops=4)

  • Sort Key: m.media
  • Sort Method: quicksort Memory: 5175kB
5. 218.033 242.768 ↑ 1.3 57,390 4

Nested Loop (cost=0.17..227,025.94 rows=74,051 width=28) (actual time=0.075..242.768 rows=57,390 loops=4)

6. 24.731 24.731 ↑ 1.3 57,390 4

Parallel Index Only Scan using media_messages_migration_pkey on media_messages_migration mmm (cost=0.08..4,192.16 rows=74,051 width=4) (actual time=0.026..24.731 rows=57,390 loops=4)

  • Heap Fetches: 65012
7. 0.004 0.004 ↑ 1.0 1 229,559

Index Scan using messages_pkey on messages m (cost=0.09..3.01 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=229,559)

  • Index Cond: (id = mmm.message_id)
  • Filter: (selected_attachments IS NULL)
8. 1,505.406 1,689.561 ↓ 1.1 388,084 4

Sort (cost=57,476.52..57,660.92 rows=368,804 width=8) (actual time=1,668.272..1,689.561 rows=388,084 loops=4)

  • Sort Key: ((me.id)::text)
  • Sort Method: quicksort Memory: 29721kB
9. 184.155 184.155 ↓ 1.0 371,903 4

Seq Scan on media me (cost=0.00..50,656.41 rows=368,804 width=8) (actual time=0.023..184.155 rows=371,903 loops=4)

10. 62.694 66.095 ↓ 87.3 1,052,238 4

Sort (cost=391.45..397.48 rows=12,047 width=31) (actual time=19.023..66.095 rows=1,052,238 loops=4)

  • Sort Key: ((('{'::text || shv.tag) || '}'::text))
  • Sort Method: quicksort Memory: 1499kB
11. 3.401 3.401 ↓ 1.0 12,125 4

Seq Scan on social_helper_videos shv (cost=0.00..228.14 rows=12,047 width=31) (actual time=0.024..3.401 rows=12,125 loops=4)