explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XOZC : Slow Query with "limit"

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 5,856.641 ↑ 1.0 1 1

Limit (cost=1,003.37..8,332.65 rows=1 width=76) (actual time=5,856.641..5,856.641 rows=1 loops=1)

  • Output: series.source_id, (array_agg(DISTINCT series.region_id)), (array_agg(DISTINCT series.item_id)), (min(series.start_date)), (max(series.end_date))
  • Buffers: shared hit=2,690,756 read=15
  • I/O Timings: read=0.081
2. 0.158 5,856.640 ↑ 1.0 1 1

GroupAggregate (cost=1,003.37..8,332.65 rows=1 width=76) (actual time=5,856.639..5,856.640 rows=1 loops=1)

  • Output: series.source_id, array_agg(DISTINCT series.region_id), array_agg(DISTINCT series.item_id), min(series.start_date), max(series.end_date)
  • Group Key: series.source_id
  • Buffers: shared hit=2,690,756 read=15
  • I/O Timings: read=0.081
3. 252.241 5,856.482 ↓ 44.0 44 1

Nested Loop Semi Join (cost=1,003.37..8,332.62 rows=1 width=20) (actual time=13.376..5,856.482 rows=44 loops=1)

  • Output: series.source_id, series.region_id, series.item_id, series.start_date, series.end_date
  • Join Filter: (series.region_id = regions.id)
  • Rows Removed by Join Filter: 125,311
  • Buffers: shared hit=2,690,756 read=15
  • I/O Timings: read=0.081
4. 0.531 3.457 ↓ 474.0 474 1

Nested Loop (cost=3.37..9.10 rows=1 width=20) (actual time=0.071..3.457 rows=474 loops=1)

  • Output: series.source_id, series.region_id, series.item_id, series.start_date, series.end_date
  • Buffers: shared hit=520 read=15
  • I/O Timings: read=0.081
5. 0.001 0.057 ↑ 1.0 1 1

Limit (cost=2.80..2.81 rows=1 width=4) (actual time=0.055..0.057 rows=1 loops=1)

  • Output: series_1.source_id
  • Buffers: shared hit=9 read=1
  • I/O Timings: read=0.006
6. 0.001 0.056 ↑ 1.0 1 1

Unique (cost=2.80..2.81 rows=1 width=4) (actual time=0.055..0.056 rows=1 loops=1)

  • Output: series_1.source_id
  • Buffers: shared hit=9 read=1
  • I/O Timings: read=0.006
7. 0.020 0.055 ↑ 1.0 1 1

Sort (cost=2.80..2.81 rows=1 width=4) (actual time=0.054..0.055 rows=1 loops=1)

  • Output: series_1.source_id
  • Sort Key: series_1.source_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=9 read=1
  • I/O Timings: read=0.006
8. 0.035 0.035 ↑ 1.0 1 1

Index Scan using series_1217781310 on data.series series_1 (cost=0.56..2.79 rows=1 width=4) (actual time=0.033..0.035 rows=1 loops=1)

  • Output: series_1.source_id
  • Index Cond: ((series_1.metric_id = 860,032) AND (series_1.item_id = 1,721) AND (series_1.region_id = 18) AND (series_1.frequency_id = 9))
  • Filter: ((series_1.end_date >= '1955-01-01'::date) AND (series_1.start_date <= '2020-10-08'::date))
  • Buffers: shared hit=6 read=1
  • I/O Timings: read=0.006
9. 2.869 2.869 ↓ 474.0 474 1

Index Scan using series_1217781310 on data.series (cost=0.56..6.27 rows=1 width=20) (actual time=0.014..2.869 rows=474 loops=1)

  • Output: series.metric_id, series.item_id, series.region_id, series.partner_region_id, series.frequency_id, series.source_id, series.start_date, series.end_date, series.data_count, series.hierarchy_id, series.time_stamp
  • Index Cond: ((series.metric_id = 860,032) AND (series.item_id = 1,721) AND (series.frequency_id = 9) AND (series.source_id = series_1.source_id))
  • Filter: ((series.end_date >= '1955-01-01'::date) AND (series.start_date <= '2020-10-08'::date))
  • Buffers: shared hit=511 read=14
  • I/O Timings: read=0.075
10. 12.324 5,600.784 ↓ 1.4 264 474

Limit (cost=1,000.00..8,319.38 rows=184 width=4) (actual time=1.124..11.816 rows=264 loops=474)

  • Output: regions.id
  • Buffers: shared hit=2,663,153
11. 1,285.962 5,588.460 ↓ 1.4 264 474

Gather (cost=1,000.00..8,319.38 rows=184 width=4) (actual time=1.123..11.790 rows=264 loops=474)

  • Output: regions.id
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=2,663,153
12. 4,302.498 4,302.498 ↓ 1.1 88 1,422 / 3

Parallel Seq Scan on ontology.regions (cost=0.00..7,300.98 rows=77 width=4) (actual time=5.379..9.077 rows=88 loops=1,422)

  • Output: regions.id
  • Filter: (regions.level = 3)
  • Rows Removed by Filter: 65,430
  • Buffers: shared hit=2,690,236
  • Worker 0: actual time=8.088..8.317 rows=3 loops=474
  • Buffers: shared hit=784,817
  • Worker 1: actual time=8.042..8.349 rows=5 loops=474
  • Buffers: shared hit=867,058