explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 739Z : Optimization for: Optimization for: plan #347v; plan #06WZ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3.492 15.583 ↓ 2.0 218 1

Aggregate (cost=1,336.79..1,340.97 rows=110 width=116) (actual time=12.132..15.583 rows=218 loops=1)

  • Buffers: shared hit=204
2. 0.229 12.091 ↓ 1.3 332 1

Sort (cost=1,336.79..1,337.42 rows=253 width=68) (actual time=12.026..12.091 rows=332 loops=1)

  • Sort Key: cities.id
  • Sort Method: quicksort Memory: 113kB
  • Buffers: shared hit=204
3. 0.097 11.862 ↓ 1.3 332 1

Hash Join (cost=942.9..1,326.69 rows=253 width=68) (actual time=8.129..11.862 rows=332 loops=1)

  • Buffers: shared hit=204
4. 2.064 11.735 ↓ 1.3 332 1

Hash Join (cost=939.21..1,322.31 rows=253 width=65) (actual time=8.094..11.735 rows=332 loops=1)

  • Buffers: shared hit=202
5. 1.598 1.598 ↑ 1.0 19,095 1

Seq Scan on city_provider_source city_provider_source (cost=0..332.95 rows=19,095 width=17) (actual time=0.005..1.598 rows=19,095 loops=1)

  • Buffers: shared hit=142
6. 0.143 8.073 ↓ 2.0 219 1

Hash (cost=937.84..937.84 rows=110 width=52) (actual time=8.073..8.073 rows=219 loops=1)

  • Buffers: shared hit=60
7. 7.837 7.930 ↓ 2.0 219 1

Bitmap Heap Scan on cities cities (cost=88.61..937.84 rows=110 width=52) (actual time=0.341..7.93 rows=219 loops=1)

  • Filter: (('0101000020E610000000000000800F3640F4E942B024854740'::geography && _st_expand(cities.bbox, '1000000'::double precision)) AND _st_dwithin(cities.bbox, '0101000020E610000000000000800F3640F4E942B024854740'::geography, '1000000'::double precision, true))
  • Heap Blocks: exact=51
  • Buffers: shared hit=60
8. 0.093 0.093 ↑ 4.4 376 1

Bitmap Index Scan on sidx_city_geom (cost=0..88.58 rows=1,657 width=0) (actual time=0.093..0.093 rows=376 loops=1)

  • Index Cond: (cities.bbox && '0101000020E610000000000000800F3640F4E942B024854740'::geography)
  • Buffers: shared hit=6
9. 0.015 0.030 ↑ 1.0 75 1

Hash (cost=2.75..2.75 rows=75 width=11) (actual time=0.029..0.03 rows=75 loops=1)

  • Buffers: shared hit=2
10. 0.015 0.015 ↑ 1.0 75 1

Seq Scan on providers providers (cost=0..2.75 rows=75 width=11) (actual time=0.006..0.015 rows=75 loops=1)

  • Buffers: shared hit=2