<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.19298"></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=620033710-11092012>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.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=620033710-11092012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=620033710-11092012>You should make the index (create index net_geom_gist
on net_geom using gist(geom) ) and try again your query</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=620033710-11092012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=620033710-11092012>Hugues.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff size=2 face=Arial></FONT> </DIV>
<DIV align=left><FONT size=2 face=Arial></FONT> </DIV><BR>
<DIV dir=ltr lang=fr class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>De :</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>De la part de</B>
Thomas Klemmer<BR><B>Envoyé :</B> mardi 11 septembre 2012
11:09<BR><B>À :</B>
postgis-users@postgis.refractions.net<BR><B>Objet :</B> [postgis-users]
ST_DWithin is not using SpatialIndex with Subquery<BR></FONT><BR></DIV>
<DIV></DIV>Hi folks <BR><BR>first of here some system
informations:<BR><BR>Server: Ubuntu 12.04 LTS, 16GB RAM 1TB 4x SSD HW
Raid<BR><BR>"PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit"<BR>"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"<BR><BR>Database:<BR><BR>TableName:
PointTbl_v1<BR>Index:
idx_pdb_v1<BR>NumRows:
4.09806e+08<BR>TableSize: 280 GB<BR>IndexSize:
21 GB [Gist
on(point_pos)]<BR><BR>GeometryCo:
point_pos<BR>Type:
POINT<BR>Dimensions: 2<BR>SRID:
4326<BR><BR>/-----------------------------------------------<BR><BR>TableName:
PointTbl_v5<BR>Index:
idx_pdb_v5<BR>NumRows:
4.16218e+07<BR>TableSize: 19 GB<BR>IndexSize:
2344 MB [Gist on(point_pos)]<BR>Primarykey:
false<BR><BR>GeometryCo: point_pos<BR>Type:
POINT<BR>Dimensions:
2<BR>SRID:
4326<BR><BR>/-----------------------------------------------<BR><BR>TableName:
NetTbl<BR>Index:
idx_net<BR>NumRows:
270615<BR>TableSize: 195 MB<BR>IndexSize:
17 MB<BR>Primarykey:
NetTbl_pk<BR><BR><BR>GeometryCo:
net_geom<BR>Type:
LINESTRING<BR>Dimensions: 2<BR>SRID:
4326<BR><BR>Basically I'm
trying to gather all points from PointTbl_v5 / PointTbl_v1 that are close or on
a LineString in NetTbl;<BR>Here ist the Query I'm trying to
run:<BR><BR>SELECT ST_AsBinary(point_pos) AS point_pos, oid, ..., type
<BR>FROM PointTbl_v5 <BR>WHERE ST_DWithin(point_pos,(SELECT net_geom from
NetTbl where ogc_fid = 500) ,5e-05);<BR><BR>This query takes 319005 msec to
return which is very slow due to the a seq scan done on PoinDB_v5<BR><BR>EXPLAIN
ANALYSE<BR><BR>"Seq Scan on PointTbl_v5
(cost=10000000008.36..10013364820.01 rows=13873927 width=202) (actual
time=199926.978..318895.494 rows=5 loops=1)"<BR>" Filter:
st_dwithin(point_pos, $0, 5e-05::double precision)"<BR>" InitPlan 1
(returns $0)"<BR>" -> 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)"<BR>" Index Cond:
(ogc_fid = 2)"<BR>"Total runtime: 318895.583 ms"<BR><BR>The query:
<BR><BR>SELECT ST_AsText(net_geom) from NetTbl where ogc_fid = 2<BR><BR>returns
in 16 ms;<BR><BR>If I instert the Geometry by Hand into the first query like
this:<BR><BR>SELECT ST_AsBinary(point_pos) AS point_pos, oid, country,
federalstate, district, town <BR>FROM PointTbl_v5 <BR>WHERE
ST_DWithin(point_pos, ST_GeomFromText(
'LINESTRING(....)',4326),5e-05);<BR><BR>This query return in 63ms on the small
table and 766ms on the bigger table.<BR><BR>EXPLAIN ANALYSE<BR><BR>"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)"<BR>" Index Cond: (point_pos
&& '0103.....A40'::geometry)"<BR>" Filter:
(('0102.....4A40'::geometry && st_expand(point_pos, 5e-05::double
precision)) AND _st_dwithin(point_pos, '01020.....A40'::geometry, 5e-05::double
precision))"<BR>"Total runtime: 1.080 ms"<BR><BR>Does anybody have a clue why
the first query with the subquery for the LineString Geometry withing ST_DWithin
not using the Spatial index?<BR><BR>seqscan_enabled is turned
off...<BR><BR>cheers Thomas<BR></BODY></HTML>