explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UXWr

Settings
# exclusive inclusive rows x rows loops node
1. 935.315 6,878.749 ↓ 1.4 378,373 1

Sort (cost=1,303,176.32..1,303,860.60 rows=273,713 width=208) (actual time=6,738.962..6,878.749 rows=378,373 loops=1)

  • Sort Key: annotations_token.example_id, (int4range((CASE WHEN (prev.span && prev.prev_span) THEN lower(prev.prev_span) ELSE lower(prev.span) END), max(upper(prev.span)))), annotations_token.span
  • Sort Method: external merge Disk: 63456kB
2. 1,002.078 5,943.434 ↓ 1.4 378,373 1

Merge Left Join (cost=225,766.76..1,224,192.88 rows=273,713 width=208) (actual time=2,994.049..5,943.434 rows=378,373 loops=1)

  • Merge Cond: (annotations_token.example_id = prev.example_id)
  • Join Filter: (annotations_token.span && (int4range((CASE WHEN (prev.span && prev.prev_span) THEN lower(prev.prev_span) ELSE lower(prev.span) END), max(upper(prev.span)))))
  • Rows Removed by Join Filter: 1689385
3. 1,123.451 1,123.451 ↓ 1.2 313,287 1

Index Scan using annotations_token_example_id_b18e09bd on annotations_token (cost=0.42..33,093.30 rows=266,725 width=144) (actual time=1.978..1,123.451 rows=313,287 loops=1)

4. 484.442 3,817.905 ↓ 50.4 2,067,757 1

Materialize (cost=225,766.34..232,539.21 rows=41,048 width=48) (actual time=2,992.062..3,817.905 rows=2,067,757 loops=1)

5. 228.347 3,333.463 ↓ 4.9 202,074 1

GroupAggregate (cost=225,766.34..232,026.11 rows=41,048 width=52) (actual time=2,992.057..3,333.463 rows=202,074 loops=1)

  • Group Key: prev.example_id, (CASE WHEN (prev.span && prev.prev_span) THEN lower(prev.prev_span) ELSE lower(prev.span) END)
6. 286.075 3,105.116 ↑ 1.2 355,731 1

Sort (cost=225,766.34..226,792.53 rows=410,476 width=84) (actual time=2,992.040..3,105.116 rows=355,731 loops=1)

  • Sort Key: prev.example_id, (CASE WHEN (prev.span && prev.prev_span) THEN lower(prev.prev_span) ELSE lower(prev.span) END)
  • Sort Method: external merge Disk: 19576kB
7. 208.065 2,819.041 ↑ 1.2 355,731 1

Subquery Scan on prev (cost=152,460.89..167,853.74 rows=410,476 width=84) (actual time=2,072.420..2,819.041 rows=355,731 loops=1)

8. 363.574 2,610.976 ↑ 1.2 355,731 1

WindowAgg (cost=152,460.89..160,670.41 rows=410,476 width=96) (actual time=2,072.416..2,610.976 rows=355,731 loops=1)

9. 468.652 2,247.402 ↑ 1.2 355,731 1

Sort (cost=152,460.89..153,487.08 rows=410,476 width=48) (actual time=2,072.397..2,247.402 rows=355,731 loops=1)

  • Sort Key: data.example_id, data.span
  • Sort Method: external merge Disk: 13976kB
10. 162.548 1,778.750 ↑ 1.2 355,731 1

Subquery Scan on data (cost=49,565.31..101,562.28 rows=410,476 width=48) (actual time=1,201.383..1,778.750 rows=355,731 loops=1)

11. 0.000 1,616.202 ↑ 1.2 355,731 1

Gather Merge (cost=49,565.31..97,457.52 rows=410,476 width=64) (actual time=1,201.382..1,616.202 rows=355,731 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 1,694.883 3,735.372 ↑ 1.7 118,577 3

Sort (cost=48,565.29..49,078.38 rows=205,238 width=64) (actual time=1,184.854..1,245.124 rows=118,577 loops=3)

  • Sort Key: annotations_token_1.span
  • Sort Method: external merge Disk: 4648kB
  • Worker 0: Sort Method: external merge Disk: 4696kB
  • Worker 1: Sort Method: external merge Disk: 4640kB
13. 1,004.787 2,040.489 ↑ 1.7 118,577 3

Parallel Hash Join (cost=9,369.54..22,738.68 rows=205,238 width=64) (actual time=508.263..680.163 rows=118,577 loops=3)

  • Hash Cond: (annotations_taggedtoken.token_id = annotations_token_1.id)
14. 367.701 367.701 ↑ 1.7 118,577 3

Parallel Seq Scan on annotations_taggedtoken (cost=0.00..9,630.38 rows=205,238 width=16) (actual time=0.006..122.567 rows=118,577 loops=3)

15. 406.503 668.001 ↑ 1.1 104,429 3

Parallel Hash (cost=6,786.35..6,786.35 rows=111,135 width=64) (actual time=222.667..222.667 rows=104,429 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 3616kB
16. 261.498 261.498 ↑ 1.1 104,429 3

Parallel Seq Scan on annotations_token annotations_token_1 (cost=0.00..6,786.35 rows=111,135 width=64) (actual time=0.006..87.166 rows=104,429 loops=3)