explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oaHI

Settings
# exclusive inclusive rows x rows loops node
1. 0.349 73.629 ↓ 10.0 10 1

GroupAggregate (cost=11,765.90..11,765.92 rows=1 width=37) (actual time=73.215..73.629 rows=10 loops=1)

  • Group Key: shapes.id, shapes.name
  • Buffers: shared hit=10751
2. 0.790 73.280 ↓ 1,964.0 1,964 1

Sort (cost=11,765.90..11,765.91 rows=1 width=29) (actual time=73.148..73.280 rows=1,964 loops=1)

  • Sort Key: shapes.id, shapes.name
  • Sort Method: quicksort Memory: 202kB
  • Buffers: shared hit=10711
3. 0.352 72.490 ↓ 1,964.0 1,964 1

Nested Loop (cost=0.54..11,765.89 rows=1 width=29) (actual time=0.341..72.490 rows=1,964 loops=1)

  • Buffers: shared hit=10671
4. 0.066 0.066 ↑ 1.0 11 1

Seq Scan on user_shapes shapes (cost=0.00..13.27 rows=11 width=742) (actual time=0.033..0.066 rows=11 loops=1)

  • Filter: ((region_id = 'arlington'::text) AND (layer_name = 'Major Transit Stops'::text))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=13
5. 72.072 72.072 ↓ 179.0 179 11

Index Scan using trips_end_point_geog_idx on trips (cost=0.54..1,068.41 rows=1 width=32) (actual time=0.195..6.552 rows=179 loops=11)

  • Index Cond: (end_point_geog && _st_expand(shapes.geog, '100'::double precision))
  • Filter: ((start_time >= '2019-01-01 05:00:00+00'::timestamp with time zone) AND (start_time < '2019-03-07 05:00:00+00'::timestamp with time zone) AND ((region_id)::text = 'arlington'::text) AND (shapes.geog && _st_expand(end_point_geog, '100'::double precision)) AND _st_dwithin(shapes.geog, end_point_geog, '100'::double precision, true))
  • Rows Removed by Filter: 759
  • Buffers: shared hit=10658