explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NvxM : Optimization for: test; plan #JeEx

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 403.702 7,685.847 ↓ 1.2 1,099,635 1

Limit (cost=545,380.88..547,596.57 rows=886,278 width=36) (actual time=6,893.664..7,685.847 rows=1,099,635 loops=1)

2.          

CTE tv_shows_list

3. 526.491 526.491 ↓ 2.6 200,000 1

Seq Scan on tv_shows (cost=0.00..51,322.85 rows=78,317 width=66) (actual time=0.047..526.491 rows=200,000 loops=1)

  • Filter: ((indexed <> '{}'::character varying[]) AND (indexed @> '{wog}'::character varying[]))
  • Rows Removed by Filter: 307937
4. 1,094.577 7,282.145 ↓ 1.2 1,099,635 1

Sort (cost=494,058.03..496,273.73 rows=886,278 width=36) (actual time=6,893.661..7,282.145 rows=1,099,635 loops=1)

  • Sort Key: tv_shows_list.watches DESC
  • Sort Method: external merge Disk: 64528kB
5. 447.323 6,187.568 ↓ 1.2 1,099,635 1

Append (cost=0.00..382,267.79 rows=886,278 width=36) (actual time=0.050..6,187.568 rows=1,099,635 loops=1)

6. 710.637 710.637 ↓ 2.6 200,000 1

CTE Scan on tv_shows_list (cost=0.00..1,566.34 rows=78,317 width=36) (actual time=0.050..710.637 rows=200,000 loops=1)

7. 115.032 249.248 ↓ 2.1 50,944 1

Hash Join (cost=7,537.97..18,512.88 rows=24,337 width=36) (actual time=76.771..249.248 rows=50,944 loops=1)

  • Hash Cond: (tv_shows_list_1.id = seasons.tv_show_id)
8. 57.543 57.543 ↓ 2.6 200,000 1

CTE Scan on tv_shows_list tv_shows_list_1 (cost=0.00..1,566.34 rows=78,317 width=36) (actual time=0.011..57.543 rows=200,000 loops=1)

9. 16.926 76.673 ↓ 2.7 50,975 1

Hash (cost=7,299.46..7,299.46 rows=19,081 width=8) (actual time=76.673..76.673 rows=50,975 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2504kB
10. 59.747 59.747 ↓ 2.7 50,975 1

Seq Scan on seasons (cost=0.00..7,299.46 rows=19,081 width=8) (actual time=0.016..59.747 rows=50,975 loops=1)

  • Filter: ((indexed <> '{}'::character varying[]) AND (indexed @> '{wog}'::character varying[]))
  • Rows Removed by Filter: 85389
11. 1,217.650 4,780.360 ↓ 1.1 848,691 1

Merge Join (cost=339,460.12..354,108.96 rows=783,624 width=36) (actual time=2,741.427..4,780.360 rows=848,691 loops=1)

  • Merge Cond: (tv_shows_list_2.id = episodes.tv_show_id)
12. 226.328 293.818 ↓ 2.5 199,529 1

Sort (cost=10,074.35..10,270.14 rows=78,317 width=36) (actual time=202.650..293.818 rows=199,529 loops=1)

  • Sort Key: tv_shows_list_2.id
  • Sort Method: external merge Disk: 10192kB
13. 67.490 67.490 ↓ 2.6 200,000 1

CTE Scan on tv_shows_list tv_shows_list_2 (cost=0.00..1,566.34 rows=78,317 width=36) (actual time=0.005..67.490 rows=200,000 loops=1)

14. 357.080 3,268.892 ↓ 3.9 848,869 1

Materialize (cost=329,385.76..330,473.44 rows=217,536 width=12) (actual time=2,538.749..3,268.892 rows=848,869 loops=1)

15. 865.681 2,911.812 ↓ 3.9 848,869 1

Sort (cost=329,385.76..329,929.60 rows=217,536 width=12) (actual time=2,538.746..2,911.812 rows=848,869 loops=1)

  • Sort Key: episodes.tv_show_id
  • Sort Method: external merge Disk: 18184kB
16. 2,046.131 2,046.131 ↓ 3.9 848,869 1

Seq Scan on episodes (cost=0.00..306,379.72 rows=217,536 width=12) (actual time=0.084..2,046.131 rows=848,869 loops=1)

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