That did it! I had to alter the query a bit and I ended up with this:<br><br>SELECT ST_AsText(point_pos) AS point_pos <br>FROM PointTbl_v5 a, NebTb1 b<br>WHERE ST_DWithin(point_pos,net_geom ,5e-05)<br>and b.ogc_fid =500;<br>
<br>The query return immediately and the Explain looks a lot different...<br><br>"Nested Loop (cost=0.00..919.32 rows=1 width=128) (actual time=2.050..12.916 rows=8 loops=1)"<br>" Join Filter: ((b.NebTb1 && st_expand(a.point_pos, 5e-05::double precision)) AND _st_dwithin(a.point_pos, b.net_geom, 5e-05::double precision))"<br>
" -> Index Scan using NetTbl_pk on NebTb1 b (cost=0.00..8.36 rows=1 width=847) (actual time=0.027..0.029 rows=1 loops=1)"<br>" Index Cond: (ogc_fid = 23546)"<br>" -> Index Scan using idx_pdb_v5 on PointTbl_v5 a (cost=0.00..854.80 rows=208 width=128) (actual time=1.173..12.669 rows=18 loops=1)"<br>
" Index Cond: (point_pos && st_expand(b.net_geom, 5e-05::double precision))"<br>"Total runtime: 13.002 ms"<br><br>Apperantly taking the ogc_fid = 500 out of the St_DWithin did the trick!<br>
<br>Thanks Francois / Hugues !? (I'm kind of confused which one your firstname is? ) :)<br><br>cheers <br><br>Thomas<br><br><div class="gmail_quote">2012/9/11 Francois Hugues <span dir="ltr"><<a href="mailto:hugues.francois@irstea.fr" target="_blank"><span style="font-family:arial,helvetica,sans-serif">hugues.francois</span>@irstea.fr</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Ok ! Give me one more try and after that someone more experienced than me should answer your question.<br>
<br>
Maybe the problem comes from the sub query into the st_dwithin<br>
<br>
Did you try something like that ?<br>
<br>
SELECT ST_AsBinary(point_pos) AS point_pos, a.*<br>
FROM PointTbl_v5 a, NebTb1<br>
WHERE ST_DWithin(point_pos,net_geom ,5e-05)<br>
and NetTbl where ogc_fid =500;<br>
<br>
Hugues.<br>
<br>
<br>
-------- Message d'origine--------<br>
De: <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a> de la part de Thomas Klemmer<br>
Date: mar. 11/09/2012 13:01<br>
À: PostGIS Users Discussion<br>
Objet : Re: [postgis-users] ST_DWithin is not using SpatialIndex withSubquery<br>
<br>
Hi hugues,<br>
<br>
net_geom has a spatial index, the postam is just using the primary key on<br>
this tabel since I am not useing any spatial filter to get the LINESTING<br>
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<br>
be used since the ST_DWithin is a spatial filter thus the spatial index<br>
should be used.<br>
<br>
TK<br>
<br>
2012/9/11 Francois Hugues <<a href="mailto:hugues.francois@irstea.fr">hugues.francois@irstea.fr</a>><br>
<br>
> **<br>
> I think your query does not use the index on net_geom because it is not a<br>
> spatial index (not a gist one) on the geometry, but a btree index on the id.<br>
><br>
> You should make the index (create index net_geom_gist on net_geom using<br>
> gist(geom) ) and try again your query<br>
><br>
> Hugues.<br>
><br>
><br>
><br>
> ------------------------------<br>
> *De :* <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a> [mailto:<br>
> <a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>] *De la part de* Thomas<br>
> Klemmer<br>
> *Envoyé :* mardi 11 septembre 2012 11:09<br>
> *À :* <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
> *Objet :* [postgis-users] ST_DWithin is not using SpatialIndex with<br>
> Subquery<br>
><br>
> 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<br>
> (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<br>
> September 2009" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8"<br>
> 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<br>
> 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 =<br>
> 500) ,5e-05);<br>
><br>
> This query takes 319005 msec to return which is very slow due to the a seq<br>
> scan done on PoinDB_v5<br>
><br>
> EXPLAIN ANALYSE<br>
><br>
> "Seq Scan on PointTbl_v5 (cost=10000000008.36..10013364820.01<br>
> rows=13873927 width=202) (actual time=199926.978..318895.494 rows=5<br>
> 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<br>
> 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,<br>
> district, town<br>
> FROM PointTbl_v5<br>
> WHERE ST_DWithin(point_pos, ST_GeomFromText(<br>
> '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<br>
> 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,<br>
> 5e-05::double precision)) AND _st_dwithin(point_pos,<br>
> '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<br>
> LineString Geometry withing ST_DWithin not using the Spatial index?<br>
><br>
> seqscan_enabled is turned off...<br>
><br>
> cheers Thomas<br>
><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>
><br>
<br>
<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>