explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YBDe : Optimization for: plan #YEws

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 183.005 183.005 ↑ 65,756.1 395 1

CTE Scan on matched_schedules (cost=27,505,825.15..29,259,047.65 rows=25,973,667 width=12) (actual time=180.664..183.005 rows=395 loops=1)

  • Output: matched_schedules.schedule_id, matched_schedules.schedule_time
  • Filter: (matched_schedules.row_number <= matched_schedules.schedule_limit)
  • Rows Removed by Filter: 1155
  • Buffers: shared hit=47502 dirtied=12
2.          

CTE schedule_details

3. 2.795 61.205 ↓ 1.0 511 1

Nested Loop (cost=170.64..100,565.70 rows=510 width=118) (actual time=1.362..61.205 rows=511 loops=1)

  • Output: s.id, s.start, s."end", s.full_scrape, s.backfill, ds.max_concurrency, CASE WHEN (NOT s.full_scrape) THEN ds.max_concurrency ELSE 1 END, CASE WHEN ((NOT s.full_scrape) AND s.backfill) THEN (s.initial_date)::timestamp with time zone WHEN ((NOT s.full_scrape) AND (NOT s.backfill)) THEN COALESCE((min(harvester_cc_taskstatistic.schedule_dttm)), ($4)) ELSE COALESCE((max(harvester_cc_taskstatistic.schedule_dttm)), ($4)) END, CASE WHEN (s.run_days IS NULL) THEN '{}'::integer[] ELSE (regexp_split_to_array((s.run_days)::text, ','::text))::integer[] END, (SubPlan 1), (((COALESCE(s.min_interval, 1440))::text || ' minutes'::text))::interval
  • Buffers: shared hit=32671
4. 0.449 50.234 ↓ 1.0 511 1

Nested Loop (cost=170.17..94,908.03 rows=510 width=52) (actual time=1.244..50.234 rows=511 loops=1)

  • Output: s.id, s.start, s."end", s.full_scrape, s.backfill, s.initial_date, s.run_days, s.min_interval, ds.max_concurrency, (min(harvester_cc_taskstatistic.schedule_dttm)), (max(harvester_cc_taskstatistic.schedule_dttm))
  • Buffers: shared hit=32008
5. 0.730 5.328 ↓ 1.0 511 1

Nested Loop (cost=0.82..8,525.91 rows=510 width=36) (actual time=0.038..5.328 rows=511 loops=1)

  • Output: s.id, s.start, s."end", s.full_scrape, s.backfill, s.initial_date, s.run_days, s.min_interval, ds.max_concurrency
  • Buffers: shared hit=3335
6. 0.510 0.510 ↓ 1.0 511 1

Index Scan using harvester_cc_schedule_pkey on public.harvester_cc_schedule s (cost=0.27..53.53 rows=510 width=36) (actual time=0.018..0.510 rows=511 loops=1)

  • Output: s.id, s.description, s.enabled, s."interval", s.start, s."end", s.min_interval, s.exclude_start, s.exclude_end, s.max_retries, s.keyword_args, s.keyword_args_merge, s.result_fields, s.result_map_merge, s.result_map, s.result_fields_merge, s.source_id, s.run_days, s.backfill, s.full_scrape, s.initial_date
  • Buffers: shared hit=269
7. 1.022 4.088 ↑ 1.0 1 511

Limit (cost=0.55..16.59 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=511)

  • Output: ds.max_concurrency
  • Buffers: shared hit=3066
8. 1.022 3.066 ↑ 1.0 1 511

Nested Loop (cost=0.55..16.59 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=511)

  • Output: ds.max_concurrency
  • Buffers: shared hit=3066
9. 1.022 1.022 ↑ 1.0 1 511

Index Scan using harvester_cc_dsvariant_pkey on public.harvester_cc_dsvariant dsv (cost=0.27..8.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=511)

  • Output: dsv.id, dsv.variant_name, dsv.enable, dsv.other_keyword_args, dsv.keyword_args_merge, dsv.other_result_fields, dsv.result_map_merge, dsv.other_result_map, dsv.result_fields_merge, dsv.parent_id
  • Index Cond: (s.source_id = dsv.id)
  • Buffers: shared hit=1533
10. 1.022 1.022 ↑ 1.0 1 511

Index Scan using harvester_cc_datasource_pkey on public.harvester_cc_datasource ds (cost=0.28..8.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=511)

  • Output: ds.id, ds.name, ds.display_name, ds.description, ds.site_url, ds.source_path, ds.max_nodes, ds.max_nodes_per_host, ds.enabled, ds.is_bulk, ds.is_realtime, ds.proxy, ds.keyword_args, ds.result_map, ds.result_fields, ds.max_concurrency
  • Index Cond: (ds.id = dsv.parent_id)
  • Buffers: shared hit=1533
11. 17.885 44.457 ↑ 1.0 1 511

Aggregate (cost=169.35..169.36 rows=1 width=16) (actual time=0.086..0.087 rows=1 loops=511)

  • Output: max(harvester_cc_taskstatistic.schedule_dttm), min(harvester_cc_taskstatistic.schedule_dttm)
  • Buffers: shared hit=28673
12. 26.572 26.572 ↑ 1.0 73 511

Index Scan using harvester_cc_taskstatistic_schedule_id_e2ba2f68 on public.harvester_cc_taskstatistic (cost=0.29..168.97 rows=75 width=8) (actual time=0.003..0.052 rows=73 loops=511)

  • Output: harvester_cc_taskstatistic.taskid, harvester_cc_taskstatistic.statedata, harvester_cc_taskstatistic.dynamodata, harvester_cc_taskstatistic.updated_dttm, harvester_cc_taskstatistic.complete, harvester_cc_taskstatistic.error, harvester_cc_taskstatistic.start_dttm, harvester_cc_taskstatistic.end_dttm, harvester_cc_taskstatistic.datasource_id, harvester_cc_taskstatistic.schedule_id, harvester_cc_taskstatistic.schedule_dttm
  • Index Cond: (harvester_cc_taskstatistic.schedule_id = s.id)
  • Buffers: shared hit=28673
13. 0.453 0.511 ↑ 1.0 1 511

Materialize (cost=0.48..0.50 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=511)

  • Output: ($4)
  • Buffers: shared hit=4
14. 0.004 0.058 ↑ 1.0 1 1

Result (cost=0.48..0.49 rows=1 width=8) (actual time=0.057..0.058 rows=1 loops=1)

  • Output: $4
  • Buffers: shared hit=4
15.          

Initplan (forResult)

16. 0.000 0.054 ↑ 1.0 1 1

Limit (cost=0.42..0.48 rows=1 width=8) (actual time=0.053..0.054 rows=1 loops=1)

  • Output: django_cron_cronjoblog.start_time
  • Buffers: shared hit=4
17. 0.054 0.054 ↑ 817,763.0 1 1

Index Scan Backward using django_cron_cronjoblog_start_time_d68c0dd9 on public.django_cron_cronjoblog (cost=0.42..42,101.77 rows=817,763 width=8) (actual time=0.053..0.054 rows=1 loops=1)

  • Output: django_cron_cronjoblog.start_time
  • Index Cond: (django_cron_cronjoblog.start_time IS NOT NULL)
  • Filter: (django_cron_cronjoblog.is_success AND ((django_cron_cronjoblog.code)::text = 'harvester.apps.harvester_cc.bulk_data_jobs'::text))
  • Buffers: shared hit=4
18.          

SubPlan (forNested Loop)

19. 0.724 7.665 ↓ 0.0 0 511

Nested Loop (cost=0.14..11.06 rows=1 width=4) (actual time=0.014..0.015 rows=0 loops=511)

  • Output: CASE WHEN (hd.did = 6) THEN 0 ELSE (hd.did + 1) END
  • Buffers: shared hit=659
20. 6.643 6.643 ↓ 0.0 0 511

Seq Scan on public.harvester_cc_schedule_on_days sod (cost=0.00..2.88 rows=1 width=4) (actual time=0.011..0.013 rows=0 loops=511)

  • Output: sod.id, sod.schedule_id, sod.day_id
  • Filter: (sod.schedule_id = s.id)
  • Rows Removed by Filter: 149
  • Buffers: shared hit=511
21. 0.298 0.298 ↓ 0.0 0 149

Index Only Scan using harvester_cc_day_pkey on public.harvester_cc_day hd (cost=0.14..8.17 rows=1 width=4) (actual time=0.001..0.002 rows=0 loops=149)

  • Output: hd.did
  • Index Cond: ((hd.did = sod.day_id) AND (hd.did >= 0) AND (hd.did IS NOT NULL))
  • Heap Fetches: 74
  • Buffers: shared hit=148
22.          

CTE matched_schedules

23. 1.088 182.142 ↑ 50,271.6 1,550 1

WindowAgg (cost=25,846,839.45..27,405,259.45 rows=77,921,000 width=30) (actual time=180.652..182.142 rows=1,550 loops=1)

  • Output: sd.schedule_id, sd.full_scrape, sd.backfill, sd.max_concurrency, sd.schedule_limit, schedule_time.schedule_time, row_number() OVER (?)
  • Buffers: shared hit=47502 dirtied=12
24. 1.422 181.054 ↑ 50,271.6 1,550 1

Sort (cost=25,846,839.45..26,041,641.95 rows=77,921,000 width=22) (actual time=180.641..181.054 rows=1,550 loops=1)

  • Output: sd.schedule_id, schedule_time.schedule_time, sd.full_scrape, sd.backfill, sd.max_concurrency, sd.schedule_limit
  • Sort Key: sd.schedule_id, schedule_time.schedule_time DESC
  • Sort Method: quicksort Memory: 170kB
  • Buffers: shared hit=47502 dirtied=12
25. 1.840 179.632 ↑ 50,271.6 1,550 1

Hash Anti Join (cost=15,650.44..10,839,171.62 rows=77,921,000 width=22) (actual time=69.926..179.632 rows=1,550 loops=1)

  • Output: sd.schedule_id, schedule_time.schedule_time, sd.full_scrape, sd.backfill, sd.max_concurrency, sd.schedule_limit
  • Hash Cond: (date_trunc('second'::text, schedule_time.schedule_time) = date_trunc('second'::text, harvester_cc_taskstatistic_1.schedule_dttm))
  • Buffers: shared hit=47496 dirtied=12
26. 1.818 113.042 ↑ 99,262.4 1,570 1

Nested Loop (cost=0.03..3,781,410.83 rows=155,842,000 width=22) (actual time=4.907..113.042 rows=1,570 loops=1)

  • Output: sd.schedule_id, sd.full_scrape, sd.backfill, sd.max_concurrency, sd.schedule_limit, schedule_time.schedule_time
  • Buffers: shared hit=32671
27. 1.358 110.259 ↑ 161.5 965 1

Nested Loop (cost=0.02..664,570.81 rows=155,842 width=54) (actual time=4.880..110.259 rows=965 loops=1)

  • Output: sd.schedule_id, sd.full_scrape, sd.backfill, sd.max_concurrency, sd.schedule_limit, sd.starttime, sd.endtime, sd.mininterval, day_series.day_series
  • Buffers: shared hit=32671
28. 61.889 61.889 ↓ 1.0 511 1

CTE Scan on schedule_details sd (cost=0.00..10.20 rows=510 width=118) (actual time=1.364..61.889 rows=511 loops=1)

  • Output: sd.schedule_id, sd.starttime, sd.endtime, sd.full_scrape, sd.backfill, sd.max_concurrency, sd.schedule_limit, sd.startdttm, sd.rundays, sd.ondays, sd.mininterval
  • Buffers: shared hit=32671
29. 30.480 47.012 ↑ 153.0 2 511

Function Scan on pg_catalog.generate_series day_series (cost=0.02..1,300.01 rows=306 width=8) (actual time=0.020..0.092 rows=2 loops=511)

  • Output: day_series.day_series
  • Function Call: generate_series(((sd.startdttm)::date)::timestamp with time zone, ((now())::date)::timestamp with time zone, '1 day'::interval)
  • Filter: (((COALESCE(array_length(sd.ondays, 1), 0) > 0) AND (SubPlan 4)) OR ((COALESCE(array_length(sd.rundays, 1), 0) > 0) AND (SubPlan 5)) OR ((COALESCE(array_length(sd.ondays, 1), 0) = 0) AND (COALESCE(array_length(sd.rundays, 1), 0) = 0)))
  • Rows Removed by Filter: 31
30.          

SubPlan (forFunction Scan)

31. 0.163 0.163 ↑ 100.0 1 163

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=163)

  • Output: d.d
  • Function Call: unnest(sd.ondays)
32. 16.369 16.369 ↑ 100.0 1 16,369

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=16,369)

  • Output: d_1.d
  • Function Call: unnest(sd.rundays)
33. 0.965 0.965 ↑ 500.0 2 965

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=2 loops=965)

  • Output: schedule_time.schedule_time
  • Function Call: generate_series((day_series.day_series + (sd.starttime)::interval), CASE WHEN (sd.endtime IS NULL) THEN (day_series.day_series + '23:59:59'::interval) ELSE (day_series.day_series + (sd.endtime)::interval) END, sd.mininterval)
34. 18.408 64.750 ↓ 1.0 37,316 1

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

  • Output: harvester_cc_taskstatistic_1.schedule_dttm
  • Buckets: 65536 Batches: 1 Memory Usage: 1970kB
  • Buffers: shared hit=14825 dirtied=12
35. 46.342 46.342 ↓ 1.0 37,316 1

Seq Scan on public.harvester_cc_taskstatistic harvester_cc_taskstatistic_1 (cost=0.00..15,191.85 rows=36,685 width=8) (actual time=0.013..46.342 rows=37,316 loops=1)

  • Output: harvester_cc_taskstatistic_1.schedule_dttm
  • Buffers: shared hit=14825 dirtied=12