explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7sGi : Optimization for: Optimization for: plan #YEws; plan #YBDe

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 111.936 111.936 ↑ 68,315.9 372 1

CTE Scan on matched_schedules (cost=26,020,813.00..27,736,224.25 rows=25,413,500 width=12) (actual time=110.554..111.936 rows=372 loops=1)

  • Output: matched_schedules.schedule_id, matched_schedules.schedule_time
  • Filter: (matched_schedules.row_number <= matched_schedules.schedule_limit)
  • Rows Removed by Filter: 512
  • Buffers: shared hit=14845
2.          

CTE matched_schedules

3. 0.665 111.446 ↑ 86,244.9 884 1

WindowAgg (cost=24,496,003.00..26,020,813.00 rows=76,240,500 width=24) (actual time=110.544..111.446 rows=884 loops=1)

  • Output: sd.schedule_id, sd.schedule_limit, schedule_time.schedule_time, row_number() OVER (?)
  • Buffers: shared hit=14845
4. 0.730 110.781 ↑ 86,244.9 884 1

Sort (cost=24,496,003.00..24,686,604.25 rows=76,240,500 width=16) (actual time=110.534..110.781 rows=884 loops=1)

  • Output: sd.schedule_id, schedule_time.schedule_time, sd.schedule_limit
  • Sort Key: sd.schedule_id, schedule_time.schedule_time DESC
  • Sort Method: quicksort Memory: 94kB
  • Buffers: shared hit=14845
5. 1.091 110.051 ↑ 86,244.9 884 1

Hash Anti Join (cost=15,650.44..10,605,751.56 rows=76,240,500 width=16) (actual time=65.873..110.051 rows=884 loops=1)

  • Output: sd.schedule_id, schedule_time.schedule_time, sd.schedule_limit
  • Hash Cond: (date_trunc('second'::text, schedule_time.schedule_time) = date_trunc('second'::text, harvester_cc_taskstatistic.schedule_dttm))
  • Buffers: shared hit=14839
6. 1.152 43.440 ↑ 169,047.7 902 1

Nested Loop (cost=0.03..3,699,865.96 rows=152,481,000 width=16) (actual time=0.078..43.440 rows=902 loops=1)

  • Output: sd.schedule_id, sd.schedule_limit, schedule_time.schedule_time
  • Buffers: shared hit=14
7. 0.813 41.408 ↑ 173.3 880 1

Nested Loop (cost=0.02..650,245.94 rows=152,481 width=48) (actual time=0.065..41.408 rows=880 loops=1)

  • Output: sd.schedule_id, sd.schedule_limit, sd.start_time, sd.end_time, sd.min_interval, day_series.day_series
  • Buffers: shared hit=14
8. 0.176 0.176 ↑ 1.0 499 1

Seq Scan on public.schedule_details sd (cost=0.00..18.99 rows=499 width=83) (actual time=0.021..0.176 rows=499 loops=1)

  • Output: sd.schedule_id, sd.schedule_limit, sd.start_time, sd.end_time, sd.start_datetime, sd.run_days, sd.on_days, sd.min_interval
  • Buffers: shared hit=14
9. 24.604 40.419 ↑ 153.0 2 499

Function Scan on pg_catalog.generate_series day_series (cost=0.02..1,300.01 rows=306 width=8) (actual time=0.012..0.081 rows=2 loops=499)

  • Output: day_series.day_series
  • Function Call: generate_series(((sd.start_datetime)::date)::timestamp with time zone, ((now())::date)::timestamp with time zone, '1 day'::interval)
  • Filter: (((COALESCE(array_length(sd.on_days, 1), 0) > 0) AND (SubPlan 1)) OR ((COALESCE(array_length(sd.run_days, 1), 0) > 0) AND (SubPlan 2)) OR ((COALESCE(array_length(sd.on_days, 1), 0) = 0) AND (COALESCE(array_length(sd.run_days, 1), 0) = 0)))
  • Rows Removed by Filter: 30
10.          

SubPlan (forFunction Scan)

11. 0.220 0.220 ↑ 100.0 1 220

Function Scan on pg_catalog.unnest d (cost=0.00..1.00 rows=100 width=4) (actual time=0.001..0.001 rows=1 loops=220)

  • Output: d.d
  • Function Call: unnest(sd.on_days)
12. 15.595 15.595 ↑ 100.0 1 15,595

Function Scan on pg_catalog.unnest d_1 (cost=0.00..1.00 rows=100 width=4) (actual time=0.001..0.001 rows=1 loops=15,595)

  • Output: d_1.d
  • Function Call: unnest(sd.run_days)
13. 0.880 0.880 ↑ 1,000.0 1 880

Function Scan on pg_catalog.generate_series schedule_time (cost=0.02..10.02 rows=1,000 width=8) (actual time=0.001..0.001 rows=1 loops=880)

  • Output: schedule_time.schedule_time
  • Function Call: generate_series((day_series.day_series + (sd.start_time)::interval), CASE WHEN (sd.end_time IS NULL) THEN (day_series.day_series + '23:59:59'::interval) ELSE (day_series.day_series + (sd.end_time)::interval) END, sd.min_interval)
14. 18.998 65.520 ↓ 1.0 37,333 1

Hash (cost=15,191.85..15,191.85 rows=36,685 width=8) (actual time=65.520..65.520 rows=37,333 loops=1)

  • Output: harvester_cc_taskstatistic.schedule_dttm
  • Buckets: 65536 Batches: 1 Memory Usage: 1971kB
  • Buffers: shared hit=14825
15. 46.522 46.522 ↓ 1.0 37,333 1

Seq Scan on public.harvester_cc_taskstatistic (cost=0.00..15,191.85 rows=36,685 width=8) (actual time=0.012..46.522 rows=37,333 loops=1)

  • Output: harvester_cc_taskstatistic.schedule_dttm
  • Buffers: shared hit=14825