Clever Geek Handbook

PostgreSQL index on temporary table

I have the following PostGIS / greSQL request

SELECT luc.*
FROM spatial_derived.lucas12 luc,
  (SELECT geom
   FROM spatial_derived.germany_bld
   WHERE state = 'SN') sn
WHERE ST_Contains(sn.geom, luc.geom)

Query plan:

Nested Loop  (cost=2.45..53.34 rows=8 width=236) (actual time=1.030..26.751 rows=1282 loops=1)
  ->  Seq Scan on germany_bld  (cost=0.00..2.20 rows=1 width=18399) (actual time=0.023..0.029 rows=1 loops=1)
        Filter: ((state)::text = 'SN'::text)
        Rows Removed by Filter: 15
  ->  Bitmap Heap Scan on lucas12 luc  (cost=2.45..51.06 rows=8 width=236) (actual time=1.002..26.031 rows=1282 loops=1)
        Recheck Cond: (germany_bld.geom ~ geom)
        Filter: _st_contains(germany_bld.geom, geom)
        Rows Removed by Filter: 499
        Heap Blocks: exact=174
        ->  Bitmap Index Scan on lucas12_geom_idx  (cost=0.00..2.45 rows=23 width=0) (actual time=0.419..0.419 rows=1781 loops=1)
              Index Cond: (germany_bld.geom ~ geom)
Planning time: 0.536 ms
Execution time: 27.023 ms

which, due to the index on the geometry columns, is pretty fast. However, when I want to add a buffer to the sn polygon (1 large polygon representing the border, hence a fairly simple function):

SELECT luc.*
FROM spatial_derived.lucas12 luc,
  (SELECT ST_Buffer(geom, 30000) geom
   FROM spatial_derived.germany_bld
   WHERE state = 'SN') sn
WHERE ST_Contains(sn.geom, luc.geom)

Query plan:

Nested Loop  (cost=0.00..13234.80 rows=7818 width=236) (actual time=6221.391..1338380.257 rows=2298 loops=1)
  Join Filter: st_contains(st_buffer(germany_bld.geom, 30000::double precision), luc.geom)
  Rows Removed by Join Filter: 22637
  ->  Seq Scan on germany_bld  (cost=0.00..2.20 rows=1 width=18399) (actual time=0.018..0.036 rows=1 loops=1)
        Filter: ((state)::text = 'SN'::text)
        Rows Removed by Filter: 15
  ->  Seq Scan on lucas12 luc  (cost=0.00..1270.55 rows=23455 width=236) (actual time=0.005..25.623 rows=24935 loops=1)
Planning time: 0.271 ms
Execution time: 1338381.079 ms

request takes forever! I blame it for a non-existing index on the sn temporary table. Massive speed reduction cannot be caused ST_Buffer()

as it is very fast in itself and the buffering function is simple.

Two questions:

1) Am I correct?

2) What should I do to achieve the same speed as with the first request?

+3


source to share


2 answers


I ran into a trap. ST_Buffer()

here is not the right choice but rather ST_DWithin()

that stores the indices of each geometry column when actually doing the comparison with the bounding box. the man page for ST_Buffer () clearly states that it does not make a mistake with ST_Buffer (), but instead uses ST_DWithin () to find the radius. Since the word Buffer is used in many GIS programs, I have not considered the alternatives.

SELECT luc.*
FROM spatial_derived.lucas12 luc
JOIN spatial_derived.germany_bld sn ON ST_DWithin(sn.geom, luc.geom, 30000)
WHERE bld.state = 'SN'


works and takes only the second (2300 points in this "buffer")!

+1


source


to check if you are correct, you can leave sn as it is and apply ST_Buffer

to the connection:

SELECT luc.*
FROM spatial_derived.lucas12 luc,
  (SELECT geom
   FROM spatial_derived.germany_bld
   WHERE state = 'SN') sn
WHERE ST_Contains(ST_Buffer(sn.geom, 30000), luc.geom)

Query plan:

Nested Loop  (cost=0.00..13234.80 rows=7818 width=236) (actual time=6237.876..1340000.576 rows=2298 loops=1)
  Join Filter: st_contains(st_buffer(germany_bld.geom, 30000::double precision), luc.geom)
  Rows Removed by Join Filter: 22637
  ->  Seq Scan on germany_bld  (cost=0.00..2.20 rows=1 width=18399) (actual time=0.023..0.038 rows=1 loops=1)
        Filter: ((state)::text = 'SN'::text)
        Rows Removed by Filter: 15
  ->  Seq Scan on lucas12 luc  (cost=0.00..1270.55 rows=23455 width=236) (actual time=0.004..24.525 rows=24935 loops=1)
Planning time: 0.453 ms
Execution time: 1340001.420 ms


this request will answer your questions or first, depending on the outcome.

Update

  • Your guess seems to be wrong. ST_Buffer()

    causes a decrease in speed.
  • It looks like you are using a much wider set in use ST_Buffer

    , so an increase in time is expected. You can run explain analyze

    for both with ST_Buffer()

    and without queries , will probably display the same plans with different numbers rows

    and cost

    ...
0


source






More articles:


All Articles