0

I have an PostgreSQL Query to search Points within a polygon:

With x as  ( 
        SELECT ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[10.2224394042641,53.3854964923405],[9.96469942811056,53.3862231021486],
        [9.80686046211711,53.3866680751889],[9.77863520851381,53.3867476466485],[9.74451890688439,53.4004200297254],[9.70750550085781,53.415253450759],
        [9.69421586355894,53.4399390778887],[9.68555562083531,53.4560255607566],[9.67981687284717,53.5224855743786],[9.66908061610189,53.6468213652195],
        [9.66968531597664,53.6948785353534],[9.68051770018823,53.7224061687423],[9.7140233880184,53.7468555225375],[9.78451635542545,53.7461557135854],
        [9.84655195738723,53.7455398639311],[9.8744711735935,53.7443181453567],[10.0508522998815,53.7365998735917],[10.1759127311391,53.7311273470475],
        [10.2303418326857,53.7287455808886],[10.2710932649579,53.7235696828722],[10.2753840619092,53.723024702581],[10.3068587686052,53.5723420834081],
        [10.3068785609039,53.5441008120269],[10.3069731423962,53.4091441991596],[10.2806962045511,53.4017934143014],[10.2224394042641,53.3854964923405]]]
        ,"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}'
    ) as a
 )

SELECT id, name, latitude, longitude FROM geonames
WHERE 
St_Within(the_geom, (SELECT * FROM x ))

So as I understood when using St_Within the Query first gets an BBOX around the Geometry and searches everything within this BoundingBox and afterwards takes this hits and test which of them lies in the Geometry itself. The Query above doesn't do that and needs several seconds to execute. I need to write the extra step in the SQL of the Query (see the following Query) then it just needs few microseconds to execute.

With x as  ( 
            SELECT ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[10.2224394042641,53.3854964923405],[9.96469942811056,53.3862231021486],
            [9.80686046211711,53.3866680751889],[9.77863520851381,53.3867476466485],[9.74451890688439,53.4004200297254],[9.70750550085781,53.415253450759],
            [9.69421586355894,53.4399390778887],[9.68555562083531,53.4560255607566],[9.67981687284717,53.5224855743786],[9.66908061610189,53.6468213652195],
            [9.66968531597664,53.6948785353534],[9.68051770018823,53.7224061687423],[9.7140233880184,53.7468555225375],[9.78451635542545,53.7461557135854],
            [9.84655195738723,53.7455398639311],[9.8744711735935,53.7443181453567],[10.0508522998815,53.7365998735917],[10.1759127311391,53.7311273470475],
            [10.2303418326857,53.7287455808886],[10.2710932649579,53.7235696828722],[10.2753840619092,53.723024702581],[10.3068587686052,53.5723420834081],
            [10.3068785609039,53.5441008120269],[10.3069731423962,53.4091441991596],[10.2806962045511,53.4017934143014],[10.2224394042641,53.3854964923405]]]
            ,"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}'
        ) as a
     )
    , y as(   
        SELECT * FROM geonames
        WHERE 
        the_geom && (SELECT ST_Envelope(a) FROM x )
    )

    SELECT id, name, latitude, longitude FROM y
    WHERE 
    St_Within(the_geom, (SELECT * FROM x ))

I'm using this Query to search points in the geonames dataset (http://download.geonames.org/export/dump/) So I'm just wondering why the first Query doesn't use a BBOX ?

EDIT:

here the query plans for the first query :

"Seq Scan on geonames  (cost=0.03..3357547.37 rows=3952686 width=38) (actual time=13450.336..18408.315 rows=1375 loops=1)"
"  Filter: st_within(the_geom, $1)"
"  Rows Removed by Filter: 11856684"
"  CTE x"
"    ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)"
"  InitPlan 2 (returns $1)"
"    ->  CTE Scan on x  (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)"
"Planning time: 0.686 ms"
"Execution time: 18408.369 ms"

and the second one:

"CTE Scan on y  (cost=2344.72..2664.94 rows=395 width=56) (actual time=0.395..6.011 rows=1375 loops=1)"
"  Filter: st_within(the_geom, $3)"
"  Rows Removed by Filter: 57"
"  CTE x"
"    ->  Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)"
"  CTE y"
"    ->  Bitmap Heap Scan on geonames  (cost=39.63..2344.69 rows=1186 width=78) (actual time=0.318..1.776 rows=1432 loops=1)"
"          Recheck Cond: (the_geom && $1)"
"          Heap Blocks: exact=666"
"          InitPlan 2 (returns $1)"
"            ->  CTE Scan on x  (cost=0.00..0.02 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=1)"
"          ->  Bitmap Index Scan on idx_geonames_geom  (cost=0.00..39.31 rows=1186 width=0) (actual time=0.253..0.253 rows=1432 loops=1)"
"                Index Cond: (the_geom && $1)"
"  InitPlan 4 (returns $3)"
"    ->  CTE Scan on x x_1  (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)"
"Planning time: 57.751 ms"
"Execution time: 6.192 ms"

so somehow the index doesn't get used for the first query

2
  • What is the question?
    – pdavis
    Mar 14, 2019 at 14:42
  • sorry, I edited my post. so the question is why the first query doesn't use a bbox first to search for geometries.
    – gemo1011
    Mar 14, 2019 at 14:51

1 Answer 1

1

The problem is how you call x. It has to be evaluated for every row, so an index is of no use.

Instead, call it once and make a join:

With x as  ( 
        SELECT ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[10.2224394042641,53.3854964923405],[9.96469942811056,53.3862231021486],
        [9.80686046211711,53.3866680751889],[9.77863520851381,53.3867476466485],[9.74451890688439,53.4004200297254],[9.70750550085781,53.415253450759],
        [9.69421586355894,53.4399390778887],[9.68555562083531,53.4560255607566],[9.67981687284717,53.5224855743786],[9.66908061610189,53.6468213652195],
        [9.66968531597664,53.6948785353534],[9.68051770018823,53.7224061687423],[9.7140233880184,53.7468555225375],[9.78451635542545,53.7461557135854],
        [9.84655195738723,53.7455398639311],[9.8744711735935,53.7443181453567],[10.0508522998815,53.7365998735917],[10.1759127311391,53.7311273470475],
        [10.2303418326857,53.7287455808886],[10.2710932649579,53.7235696828722],[10.2753840619092,53.723024702581],[10.3068587686052,53.5723420834081],
        [10.3068785609039,53.5441008120269],[10.3069731423962,53.4091441991596],[10.2806962045511,53.4017934143014],[10.2224394042641,53.3854964923405]]]
        ,"crs":{"type":"name","properties":{"name":"EPSG:4326"}}}'
    ) as geom
 )

SELECT id, name, latitude, longitude 
FROM geonames JOIN x ON St_Within(the_geom, x.geom);
1
  • thank you for the fast answer. The query worked and uses the index now
    – gemo1011
    Mar 14, 2019 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.