explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2koj : Optimization for: Slow Query with "limit"; plan #XOZC

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.001 1.371 ↑ 1.0 1 1

Limit (cost=3.79..11.77 rows=1 width=76) (actual time=1.370..1.371 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,430
2. 0.029 1.370 ↑ 1.0 1 1

GroupAggregate (cost=3.79..11.77 rows=1 width=76) (actual time=1.369..1.370 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,430
3. 0.297 1.341 ↓ 44.0 44 1

Nested Loop (cost=3.79..11.75 rows=1 width=20) (actual time=0.040..1.341 rows=44 loops=1)

  • Output: series.source_id, series.region_id, series.item_id, series.start_date, series.end_date
  • Inner Unique: true
  • Buffers: shared hit=2,430
4. 0.051 0.570 ↓ 474.0 474 1

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

  • Output: series.source_id, series.region_id, series.item_id, series.start_date, series.end_date
  • Buffers: shared hit=532
5. 0.001 0.019 ↑ 1.0 1 1

Unique (cost=2.80..2.81 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1)

  • Output: series_1.source_id
  • Buffers: shared hit=7
6. 0.004 0.018 ↑ 1.0 1 1

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

  • Output: series_1.source_id
  • Sort Key: series_1.source_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=7
7. 0.014 0.014 ↑ 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.011..0.014 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=7
8. 0.500 0.500 ↓ 474.0 474 1

Index Scan using series_1217781310 on data.series (cost=0.56..6.27 rows=1 width=20) (actual time=0.012..0.500 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=525
9. 0.474 0.474 ↓ 0.0 0 474

Index Scan using regions_pkey on ontology.regions (cost=0.42..2.62 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=474)

  • Output: regions.id, regions.contains, regions.name, regions.aliases, regions.level, regions.latitude, regions.longitude, regions.ranking_score, regions.historical, regions.definition
  • Index Cond: (regions.id = series.region_id)
  • Filter: (regions.level = 3)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1,898