explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2oRG

Settings
# exclusive inclusive rows x rows loops node
1. 2,059.965 1,097,036.765 ↓ 0.0 0 1

Update on tmp_th_xtns_by_theater_rel_week_samerica x1 (cost=0.00..11,762.54 rows=1 width=3,063) (actual time=1,097,036.765..1,097,036.765 rows=0 loops=1)

  • Buffers: local hit=138,577,559 read=2 dirtied=9,084 written=9,082
2. 248.994 1,094,976.800 ↓ 51,134.0 51,134 1

Seq Scan on tmp_th_xtns_by_theater_rel_week_samerica x1 (cost=0.00..11,762.54 rows=1 width=3,063) (actual time=13.273..1,094,976.800 rows=51,134 loops=1)

  • Filter: (first_engagement_date IS NULL)
  • Rows Removed by Filter: 25,173
  • Buffers: local hit=137,385,176
3.          

SubPlan (for Seq Scan)

4. 51.134 1,094,727.806 ↑ 1.0 1 51,134

Limit (cost=2,117.41..2,117.42 rows=1 width=16) (actual time=21.408..21.409 rows=1 loops=51,134)

  • Buffers: local hit=137,375,720
5. 460.206 1,094,676.672 ↑ 1.0 1 51,134

Sort (cost=2,117.41..2,117.42 rows=1 width=16) (actual time=21.408..21.408 rows=1 loops=51,134)

  • Sort Key: x2.exhibition_week DESC, x2.first_engagement_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: local hit=137,375,720
6. 7,305.511 1,094,216.466 ↓ 39.0 39 51,134

Nested Loop (cost=0.29..2,117.40 rows=1 width=16) (actual time=2.967..21.399 rows=39 loops=51,134)

  • Join Filter: ((x2.exhibition_week = o.exhibition_week) AND (th_week(x2.exhibition_week, o.day1, o.rollback_date) = x2.exhibition_week))
  • Rows Removed by Join Filter: 108
  • Buffers: local hit=137,375,720
  • -> Index Scan using tmp_th_xtns_by_theater_rel_week_samerica_rel_pri_th_rel_no on tmp_th_xtns_by_theater_rel_week_samerica x2 (cost=0.29..2.31 rows=1 width=24) (actual time=0.003..0.014 rows=4 loops=51,134) Index Cond: ((primary_theater_no = x1.primary_theater_no) AND (release_no = x1.release_no))
  • Filter: ((first_engagement_date IS NOT NULL) AND (x1.exhibition_week > exhibition_week))
  • Rows Removed by Filter: 12
  • Buffers: local hit=508,665
7. 1,086,910.955 1,086,910.955 ↓ 35.0 35 216,905

Seq Scan on tmp_ood_primary_theater_engagements o (cost=0.00..2,114.83 rows=1 width=24) (actual time=2.318..5.011 rows=35 loops=216,905)

  • Filter: ((primary_theater_no = x1.primary_theater_no) AND (release_no = x1.release_no))
  • Rows Removed by Filter: 98,887
  • Buffers: local hit=136,867,055
Planning time : 0.662 ms
Execution time : 1,097,037.640 ms