explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MW5L

Settings
# exclusive inclusive rows x rows loops node
1. 125,683.087 133,957.416 ↓ 8.1 122,392,368 1

Hash Join (cost=1,450,111.72..4,464,332.51 rows=15,021,181 width=44) (actual time=8,206.990..133,957.416 rows=122,392,368 loops=1)

  • Hash Cond: (duos.log_id = pdp_duos.log_id)
  • Join Filter: ((duos.effective_from <= c.settlement_date) AND (duos.effective_to >= c.settlement_date))
  • Buffers: shared hit=259508, temp read=25605 written=25605
2. 95.320 95.320 ↑ 1.0 6,982 1

Seq Scan on pricing_duos_view duos (cost=0.00..577.35 rows=7,011 width=58) (actual time=0.011..95.320 rows=6,982 loops=1)

  • Filter: (((load_type)::text = ANY ('{actual,forecast}'::text[])) AND (rn = 1))
  • Rows Removed by Filter: 9108
  • Buffers: shared hit=336
3. 1,057.982 8,179.009 ↑ 1.2 2,489,184 1

Hash (cost=1,397,511.26..1,397,511.26 rows=2,864,997 width=22) (actual time=8,179.009..8,179.009 rows=2,489,184 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 256 (originally 64) Memory Usage: 3585kB
  • Buffers: shared hit=259172, temp written=14335
4. 1,157.999 7,121.027 ↑ 1.2 2,489,184 1

Nested Loop (cost=0.42..1,397,511.26 rows=2,864,997 width=22) (actual time=1.730..7,121.027 rows=2,489,184 loops=1)

  • Buffers: shared hit=259172
5. 10.452 10.452 ↑ 1.0 1,032 1

Seq Scan on pricing_duos_periods pdp_duos (cost=0.00..70.80 rows=1,032 width=48) (actual time=0.008..10.452 rows=1,032 loops=1)

  • Filter: ((period_type)::text = 'DUOS'::text)
  • Rows Removed by Filter: 1592
  • Buffers: shared hit=38
6. 5,952.576 5,952.576 ↑ 1.0 2,412 1,032

Index Scan using ix_calendar_date on calendar c (cost=0.42..1,329.76 rows=2,435 width=26) (actual time=0.028..5.768 rows=2,412 loops=1,032)

  • Index Cond: ((pdp_duos.effective_from <= settlement_date) AND (pdp_duos.effective_to >= settlement_date))
  • Filter: ((start_time >= pdp_duos.time_from) AND ((weekday_flag = pdp_duos.weekday_flag) OR (weekend_flag = pdp_duos.weekend_flag)) AND (start_datetime < ((settlement_date + pdp_duos.time_to) + ((CASE WHEN (pdp_duos.time_to = '00:00:00'::time without time zone) THEN '1'::text ELSE '0'::text END || ' day'::text))::interval)))
  • Rows Removed by Filter: 15117
  • Buffers: shared hit=259134