explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VpQS : Optimization for: plan #AQQy

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.046 2,592.885 ↑ 4,433.2 11 1

Sort (cost=28,433,499.19..28,433,621.11 rows=48,765 width=1,682) (actual time=2,592.884..2,592.885 rows=11 loops=1)

  • Sort Key: ss.soil_texture_id
  • Sort Method: quicksort Memory: 176kB
2. 65.690 2,592.839 ↑ 4,433.2 11 1

GroupAggregate (cost=25,937,077.20..28,394,029.97 rows=48,765 width=1,682) (actual time=2,530.046..2,592.839 rows=11 loops=1)

  • Group Key: ss.county_id, ss.mapunit_name, ss.soil_texture_id
3. 0.504 2,527.149 ↑ 67,155.7 70 1

Sort (cost=25,937,077.20..25,948,829.46 rows=4,700,902 width=1,682) (actual time=2,527.078..2,527.149 rows=70 loops=1)

  • Sort Key: ss.county_id, ss.mapunit_name, ss.soil_texture_id
  • Sort Method: quicksort Memory: 171kB
4. 46.768 2,526.645 ↑ 67,155.7 70 1

Merge Left Join (cost=15,050,207.66..15,100,743.34 rows=4,700,902 width=1,682) (actual time=2,526.028..2,526.645 rows=70 loops=1)

  • Merge Cond: ((s.fips_county_id = ss.fips_county_id) AND (s.state = ss.state) AND ((s.mapunit_symbol)::text = (ss.mapunit_symbol)::text))
5. 0.326 7.401 ↑ 67,155.7 70 1

Sort (cost=15,017,989.80..15,029,742.06 rows=4,700,902 width=1,642) (actual time=7.383..7.401 rows=70 loops=1)

  • Sort Key: s.fips_county_id, s.state, s.mapunit_symbol
  • Sort Method: quicksort Memory: 168kB
6. 0.049 7.075 ↑ 67,155.7 70 1

Nested Loop (cost=42.97..4,422,672.94 rows=4,700,902 width=1,642) (actual time=0.370..7.075 rows=70 loops=1)

7. 0.005 0.005 ↑ 1,230.0 1 1

Seq Scan on field_boundary_192097 boundary (cost=0.00..22.30 rows=1,230 width=32) (actual time=0.004..0.005 rows=1 loops=1)

8. 6.879 7.021 ↑ 5.5 70 1

Bitmap Heap Scan on ssurgo s (cost=42.97..3,591.83 rows=382 width=1,610) (actual time=0.362..7.021 rows=70 loops=1)

  • Recheck Cond: (boundary.the_geom && the_geom)
  • Filter: _st_intersects(boundary.the_geom, the_geom)
  • Rows Removed by Filter: 17
  • Heap Blocks: exact=83
9. 0.142 0.142 ↑ 13.2 87 1

Bitmap Index Scan on ssurgo_the_geom_idx (cost=0.00..42.87 rows=1,147 width=0) (actual time=0.142..0.142 rows=87 loops=1)

  • Index Cond: (boundary.the_geom && the_geom)
10. 41.413 2,472.476 ↑ 2.2 96,426 1

Materialize (cost=32,217.86..33,285.85 rows=213,599 width=62) (actual time=2,136.817..2,472.476 rows=96,426 loops=1)

11. 2,350.415 2,431.063 ↑ 2.2 96,367 1

Sort (cost=32,217.86..32,751.85 rows=213,599 width=62) (actual time=2,136.813..2,431.063 rows=96,367 loops=1)

  • Sort Key: ss.fips_county_id, ss.state, ss.mapunit_symbol
  • Sort Method: external merge Disk: 15512kB
12. 80.648 80.648 ↑ 1.0 213,599 1

Seq Scan on ssurgo_soil ss (cost=0.00..5,276.99 rows=213,599 width=62) (actual time=0.006..80.648 rows=213,599 loops=1)