explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SCbc : OSM US West - Trees Near Benchs by County

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.937 1,547.867 ↑ 6,273.6 1,728 1

Sort (cost=126,390,563.58..126,417,665.43 rows=10,840,743 width=58) (actual time=1,547.607..1,547.867 rows=1,728 loops=1)

  • Output: b.name, p.osm_id, p.way, (count(t.osm_id))
  • Sort Key: (count(t.osm_id)) DESC
  • Sort Method: quicksort Memory: 292kB
  • Buffers: shared hit=190,753
  • Settings: jit = 'off', max_parallel_workers_per_gather = '3', random_page_cost = '1.9'
2. 1.888 1,546.930 ↑ 6,273.6 1,728 1

Finalize GroupAggregate (cost=120,997,234.29..124,267,888.01 rows=10,840,743 width=58) (actual time=1,540.092..1,546.930 rows=1,728 loops=1)

  • Output: b.name, p.osm_id, p.way, count(t.osm_id)
  • Group Key: b.name, p.osm_id, p.way
  • Buffers: shared hit=190,753
3. 33.484 1,545.042 ↑ 6,273.6 1,728 1

Gather Merge (cost=120,997,234.29..124,051,073.15 rows=10,840,743 width=58) (actual time=1,540.075..1,545.042 rows=1,728 loops=1)

  • Output: b.name, p.osm_id, p.way, (PARTIAL count(t.osm_id))
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=381,610
4. 2.135 1,511.558 ↑ 12,547.2 864 2 / 2

Partial GroupAggregate (cost=120,996,234.28..122,830,489.55 rows=10,840,743 width=58) (actual time=1,509.194..1,511.558 rows=864 loops=2)

  • Output: b.name, p.osm_id, p.way, PARTIAL count(t.osm_id)
  • Group Key: b.name, p.osm_id, p.way
  • Buffers: shared hit=381,310
  • Worker 0: actual time=1,521.410..1522.203 rows=633 loops=1
  • Buffers: shared hit=190,857
5. 16.046 1,509.423 ↑ 85,438.0 1,616 2 / 2

Sort (cost=120,996,234.28..121,341,403.85 rows=138,067,827 width=58) (actual time=1,509.176..1,509.423 rows=1,616 loops=2)

  • Output: b.name, p.osm_id, p.way, t.osm_id
  • Sort Key: b.name, p.osm_id, p.way
  • Sort Method: quicksort Memory: 393kB
  • Worker 0: Sort Method: quicksort Memory: 206kB
  • Buffers: shared hit=380,152
  • Worker 0: actual time=1,521.395..1521.494 rows=1,123 loops=1
  • Buffers: shared hit=190,857
6. 30.515 1,493.377 ↑ 85,438.0 1,616 2 / 2

Nested Loop (cost=0.82..91,427,750.62 rows=138,067,827 width=58) (actual time=22.070..1,493.377 rows=1,616 loops=2)

  • Output: b.name, p.osm_id, p.way, t.osm_id
  • Buffers: shared hit=362,329
  • Worker 0: actual time=13.661..1519.191 rows=1,123 loops=1
  • Buffers: shared hit=190,833
7. 12.202 927.905 ↑ 5.3 11,630 2 / 2

Nested Loop (cost=0.28..705,585.54 rows=61,382 width=50) (actual time=0.416..927.905 rows=11,630 loops=2)

  • Output: p.osm_id, p.way, b.name
  • Buffers: shared hit=265,218
  • Worker 0: actual time=0.586..947.214 rows=10,742 loops=1
  • Buffers: shared hit=142,470
8. 2.981 2.981 ↑ 1.2 230 2 / 2

Parallel Seq Scan on osm.boundary_polygon b (cost=0.00..1,456.47 rows=270 width=8,008) (actual time=0.013..2.981 rows=230 loops=2)

  • Output: b.osm_id, b.name, b.admin_level, b.boundary, b.way, b.code
  • Filter: (b.admin_level = '6'::text)
  • Rows Removed by Filter: 1,774
  • Buffers: shared hit=1,427
  • Worker 0: actual time=0.011..3.642 rows=226 loops=1
  • Buffers: shared hit=558
9. 912.721 912.721 ↓ 25.5 51 459 / 2

Index Scan using gix_osm_miscpoi_point on osm.miscpoi_point p (cost=0.28..2,607.87 rows=2 width=40) (actual time=1.041..3.977 rows=51 loops=459)

  • Output: p.osm_id, p.name, p.way, p.code
  • Index Cond: (p.way @ b.way)
  • Filter: ((p.code = '2902'::text) AND st_contains(b.way, p.way))
  • Rows Removed by Filter: 287
  • Buffers: shared hit=261,921
  • Worker 0: actual time=0.955..4.117 rows=48 loops=226
  • Buffers: shared hit=140,924
10. 534.957 534.957 ↓ 0.0 0 23,259 / 2

Index Scan using gix_osm_natural_point on osm.natural_point t (cost=0.54..1,477.46 rows=53 width=40) (actual time=0.045..0.046 rows=0 loops=23,259)

  • Output: t.osm_id, t.name, t."natural", t.elevation, t.way, t.code
  • Index Cond: (t.way && st_expand(p.way, '10'::double precision))
  • Filter: ((t."natural" = 'tree'::text) AND st_dwithin(p.way, t.way, '10'::double precision))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=97,111
  • Worker 0: actual time=0.050..0.051 rows=0 loops=10,742
  • Buffers: shared hit=48,363
Planning time : 2.619 ms
Execution time : 1,548.230 ms