explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JeEx : test

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 121.891 45,483.395 ↓ 1.1 1,083,591 1

Limit (cost=701,785.03..704,213.69 rows=971,463 width=36) (actual time=45,199.169..45,483.395 rows=1,083,591 loops=1)

2.          

CTE tv_shows_list

3. 433.940 433.940 ↓ 2.4 200,000 1

Seq Scan on tv_shows tv_shows_2 (cost=0.00..30,746.30 rows=82,181 width=66) (actual time=0.403..433.940 rows=200,000 loops=1)

  • Filter: ((indexed <> '{}'::character varying[]) AND (indexed @> '{wog}'::character varying[]))
  • Rows Removed by Filter: 288323
4. 599.748 45,361.504 ↓ 1.1 1,083,591 1

Sort (cost=671,038.73..673,467.39 rows=971,463 width=36) (actual time=45,199.168..45,361.504 rows=1,083,591 loops=1)

  • Sort Key: tv_shows_list.watches DESC
  • Sort Method: external merge Disk: 63768kB
5. 250.263 44,761.756 ↓ 1.1 1,083,591 1

Unique (cost=540,576.74..547,862.71 rows=971,463 width=36) (actual time=41,779.246..44,761.756 rows=1,083,591 loops=1)

6. 32,480.125 44,511.493 ↓ 1.1 1,083,843 1

Sort (cost=540,576.74..543,005.40 rows=971,463 width=36) (actual time=41,779.245..44,511.493 rows=1,083,843 loops=1)

  • Sort Key: tv_shows_list.watches, tv_shows_list.path
  • Sort Method: external merge Disk: 63760kB
7. 121.722 12,031.368 ↓ 1.1 1,083,843 1

Append (cost=0.00..417,400.72 rows=971,463 width=36) (actual time=0.406..12,031.368 rows=1,083,843 loops=1)

8. 556.572 556.572 ↓ 2.4 200,000 1

CTE Scan on tv_shows_list (cost=0.00..1,643.62 rows=82,181 width=36) (actual time=0.406..556.572 rows=200,000 loops=1)

9. 58.868 446.695 ↓ 2.1 48,937 1

Merge Join (cost=18,166.34..18,979.14 rows=22,965 width=36) (actual time=344.488..446.695 rows=48,937 loops=1)

  • Merge Cond: (tv_shows.id = seasons.tv_show_id)
10. 201.572 246.367 ↓ 2.4 199,604 1

Sort (cost=10,602.77..10,808.22 rows=82,181 width=36) (actual time=210.044..246.367 rows=199,604 loops=1)

  • Sort Key: tv_shows.id
  • Sort Method: external merge Disk: 10208kB
11. 44.795 44.795 ↓ 2.4 200,000 1

CTE Scan on tv_shows_list tv_shows (cost=0.00..1,643.62 rows=82,181 width=36) (actual time=0.027..44.795 rows=200,000 loops=1)

12. 22.135 141.460 ↓ 2.9 49,029 1

Sort (cost=7,563.58..7,606.44 rows=17,145 width=8) (actual time=134.415..141.460 rows=49,029 loops=1)

  • Sort Key: seasons.tv_show_id
  • Sort Method: quicksort Memory: 3664kB
13. 119.325 119.325 ↓ 2.9 49,029 1

Seq Scan on seasons (cost=0.00..6,357.81 rows=17,145 width=8) (actual time=2.808..119.325 rows=49,029 loops=1)

  • Filter: ((indexed <> '{}'::character varying[]) AND (indexed @> '{wog}'::character varying[]))
  • Rows Removed by Filter: 86025
14. 886.917 10,906.379 ↑ 1.0 834,906 1

Merge Join (cost=370,984.73..387,063.33 rows=866,317 width=36) (actual time=9,670.215..10,906.379 rows=834,906 loops=1)

  • Merge Cond: (tv_shows_1.id = episodes.tv_show_id)
15. 185.707 229.962 ↓ 2.4 199,604 1

Sort (cost=10,602.77..10,808.22 rows=82,181 width=36) (actual time=164.816..229.962 rows=199,604 loops=1)

  • Sort Key: tv_shows_1.id
  • Sort Method: external merge Disk: 10208kB
16. 44.255 44.255 ↓ 2.4 200,000 1

CTE Scan on tv_shows_list tv_shows_1 (cost=0.00..1,643.62 rows=82,181 width=36) (actual time=0.015..44.255 rows=200,000 loops=1)

17. 117.694 9,789.500 ↓ 4.1 835,642 1

Materialize (cost=360,381.96..361,396.27 rows=202,862 width=12) (actual time=9,505.381..9,789.500 rows=835,642 loops=1)

18. 664.218 9,671.806 ↓ 4.1 835,642 1

Sort (cost=360,381.96..360,889.11 rows=202,862 width=12) (actual time=9,505.375..9,671.806 rows=835,642 loops=1)

  • Sort Key: episodes.tv_show_id
  • Sort Method: external merge Disk: 18064kB
19. 0.000 9,007.588 ↓ 4.1 835,642 1

Gather (cost=1,000.00..339,031.03 rows=202,862 width=12) (actual time=3.125..9,007.588 rows=835,642 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
20. 9,088.805 9,088.805 ↓ 3.3 278,547 3

Parallel Seq Scan on episodes (cost=0.00..317,744.83 rows=84,526 width=12) (actual time=0.615..9,088.805 rows=278,547 loops=3)

  • Filter: ((indexed <> '{}'::character varying[]) AND (indexed @> '{wog}'::character varying[]))
  • Rows Removed by Filter: 858396