Hi hugues, <br><br>net_geom has a spatial index, the postam is just using the primary key on this tabel since I am not useing any spatial filter to get the LINESTING out of the table (just "where ogs_fid = 2" which is the primary key).<br>
<br>The cruzial part is the seq scen on the large point table which should not be used since the ST_DWithin is a spatial filter thus the spatial index should be used.<br><br>TK<br><br><div class="gmail_quote">2012/9/11 Francois Hugues <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank">hugues.francois@irstea.fr</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><u></u>
<div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>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" face="Arial"><span></span></font> </div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>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" face="Arial"><span></span></font> </div>
<div dir="ltr" align="left"><font color="#0000ff" face="Arial"><span>Hugues.</span></font></div>
<div><font color="#0000ff" face="Arial"></font> </div>
<div align="left"><font face="Arial"></font> </div><br>
<div dir="ltr" align="left" lang="fr">
<hr>
<font face="Tahoma"><b>De :</b>
<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-users-bounces@postgis.refractions.net" target="_blank">postgis-users-bounces@postgis.refractions.net</a>] <b>De la part de</b>
Thomas Klemmer<br><b>Envoyé :</b> mardi 11 septembre 2012
11:09<br><b>À :</b>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br><b>Objet :</b> [postgis-users]
ST_DWithin is not using SpatialIndex with Subquery<br></font><br></div><div><div class="h5">
<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></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>