explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YQrn : Optimization for: plan #eFXV

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 15.034 1,016.899 ↑ 1.0 10,000 1

Nested Loop (cost=129.56..1,296,626.66 rows=10,000 width=201) (actual time=0.186..1,016.899 rows=10,000 loops=1)

2. 1.865 1.865 ↑ 1.0 10,000 1

Seq Scan on test g1 (cost=0.00..725.00 rows=10,000 width=185) (actual time=0.011..1.865 rows=10,000 loops=1)

3. 10.000 1,000.000 ↑ 1.0 1 10,000

Limit (cost=129.56..129.57 rows=1 width=48) (actual time=0.100..0.100 rows=1 loops=10,000)

4. 110.000 990.000 ↑ 1.0 1 10,000

Sort (cost=129.56..129.57 rows=1 width=48) (actual time=0.099..0.099 rows=1 loops=10,000)

  • Sort Key: ((g1.geom <-> g.geom))
  • Sort Method: top-N heapsort Memory: 25kB
5. 560.000 880.000 ↓ 14.0 14 10,000

Bitmap Heap Scan on sm_lvl g (cost=4.60..129.55 rows=1 width=48) (actual time=0.041..0.088 rows=14 loops=10,000)

  • Recheck Cond: (geom && st_expand(g1.geom, '1000'::double precision))
  • Filter: (((g1.se_code)::text = (se_code)::text) AND (g1.geom && st_expand(geom, '1000'::double precision)) AND _st_dwithin(g1.geom, geom, '1000'::double precision))
  • Rows Removed by Filter: 53
  • Heap Blocks: exact=100073
6. 320.000 320.000 ↓ 2.2 67 10,000

Bitmap Index Scan on sm_lvl_geom_idx (cost=0.00..4.59 rows=31 width=0) (actual time=0.032..0.032 rows=67 loops=10,000)

  • Index Cond: (geom && st_expand(g1.geom, '1000'::double precision))