explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AQQy

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.048 2,576.667 ↑ 4,433.2 11 1

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

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

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

  • Group Key: ss.county_id, ss.mapunit_name, ss.soil_texture_id
3. 0.491 2,510.638 ↑ 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,510.564..2,510.638 rows=70 loops=1)

  • Sort Key: ss.county_id, ss.mapunit_name, ss.soil_texture_id
  • Sort Method: quicksort Memory: 171kB
4. 46.320 2,510.147 ↑ 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,509.529..2,510.147 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.338 7.475 ↑ 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.451..7.475 rows=70 loops=1)

  • Sort Key: s.fips_county_id, s.state, s.mapunit_symbol
  • Sort Method: quicksort Memory: 168kB
6. 0.066 7.137 ↑ 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.137 rows=70 loops=1)

7. 0.004 0.004 ↑ 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.003..0.004 rows=1 loops=1)

8. 6.926 7.067 ↑ 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.067 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.141 0.141 ↑ 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.141..0.141 rows=87 loops=1)

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

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

11. 2,333.730 2,416.263 ↑ 2.2 96,367 1

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

  • Sort Key: ss.fips_county_id, ss.state, ss.mapunit_symbol
  • Sort Method: external merge Disk: 15512kB
12. 82.533 82.533 ↑ 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.005..82.533 rows=213,599 loops=1)

Planning time : 1.183 ms
Execution time : 2,579.589 ms