[postgis-users] ST_DWithin is not using SpatialIndex with Subquery

[Francois Hugues]

I think your query does not use the index on net_geom because it is not a spatial index (not a gist one) on the geometry, but a btree index on the id.
 
You should make the index (create index net_geom_gist on net_geom using gist(geom) ) and try again your query
 
Hugues.
 
 

________________________________

De : postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] De la part de Thomas Klemmer
Envoyé : mardi 11 septembre 2012 11:09
À : postgis-users at postgis.refractions.net
Objet : [postgis-users] ST_DWithin is not using SpatialIndex with Subquery


Hi folks 

first of here some system informations:

Server: Ubuntu 12.04 LTS, 16GB RAM 1TB 4x SSD HW Raid

"PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit"
"POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER"

Database:

TableName:   PointTbl_v1
Index:           idx_pdb_v1
NumRows:    4.09806e+08
TableSize:     280 GB
IndexSize:     21 GB    [Gist on(point_pos)]

GeometryCo:    point_pos
Type:                POINT
Dimensions:     2
SRID:              4326

/-----------------------------------------------

TableName:    PointTbl_v5
Index:             idx_pdb_v5
NumRows:     4.16218e+07
TableSize:     19 GB
IndexSize:     2344 MB [Gist on(point_pos)]
Primarykey:    false

GeometryCo:   point_pos
Type:              POINT
Dimensions:    2
SRID:             4326

/-----------------------------------------------

TableName:    NetTbl
Index:            idx_net
NumRows:     270615
TableSize:     195 MB
IndexSize:     17 MB
Primarykey:   NetTbl_pk


GeometryCo:    net_geom
Type:                LINESTRING
Dimensions:     2
SRID:              4326

Basically I'm trying to gather all points from PointTbl_v5 / PointTbl_v1 that are close or on a LineString in NetTbl;
Here ist the Query I'm trying to run:

SELECT  ST_AsBinary(point_pos) AS point_pos, oid, ..., type 
FROM PointTbl_v5  
WHERE ST_DWithin(point_pos,(SELECT net_geom from NetTbl where ogc_fid = 500) ,5e-05);

This query takes 319005 msec to return which is very slow due to the a seq scan done on PoinDB_v5

EXPLAIN ANALYSE

"Seq Scan on PointTbl_v5  (cost=10000000008.36..10013364820.01 rows=13873927 width=202) (actual time=199926.978..318895.494 rows=5 loops=1)"
"  Filter: st_dwithin(point_pos, $0, 5e-05::double precision)"
"  InitPlan 1 (returns $0)"
"    ->  Index Scan using NetTbl_pk on NetTbl  (cost=0.00..8.36 rows=1 width=847) (actual time=2.069..2.075 rows=1 loops=1)"
"          Index Cond: (ogc_fid = 2)"
"Total runtime: 318895.583 ms"

The query: 

SELECT ST_AsText(net_geom) from NetTbl where ogc_fid = 2

returns in 16 ms;

If I instert the Geometry by Hand into the first query like this:

SELECT  ST_AsBinary(point_pos) AS point_pos, oid, country, federalstate, district, town 
FROM PointTbl_v5  
WHERE ST_DWithin(point_pos, ST_GeomFromText( 'LINESTRING(....)',4326),5e-05);

This query return in 63ms on the small table and 766ms on the bigger table.

EXPLAIN ANALYSE

"Index Scan using idx_pdb_v5 on PointTbl_v5  (cost=0.00..147.61 rows=1 width=202) (actual time=0.047..1.050 rows=23 loops=1)"
"  Index Cond: (point_pos && '0103.....A40'::geometry)"
"  Filter: (('0102.....4A40'::geometry && st_expand(point_pos, 5e-05::double precision)) AND _st_dwithin(point_pos, '01020.....A40'::geometry, 5e-05::double precision))"
"Total runtime: 1.080 ms"

Does anybody have a clue why the first query with the subquery for the LineString Geometry withing ST_DWithin not using the Spatial index?

seqscan_enabled is turned off...

cheers Thomas

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120911/54c9bd87/attachment.html>