[postgis-users] RE : RE : ST_DWithin is not using SpatialIndexwithSubquery
Francois Hugues
hugues.francois at irstea.fr
Wed Sep 12 00:20:01 PDT 2012
You're welcome :)
Sorry for the too fast copy & paste.
Don't worry about my name. Everybody confused with it ! The first name is Hugues, you were right.
Hugues.
-------- Message d'origine--------
De: postgis-users-bounces at postgis.refractions.net de la part de Thomas Klemmer
Date: mer. 12/09/2012 09:06
À: PostGIS Users Discussion
Objet : Re: [postgis-users] RE : ST_DWithin is not using SpatialIndexwithSubquery
That did it! I had to alter the query a bit and I ended up with this:
SELECT ST_AsText(point_pos) AS point_pos
FROM PointTbl_v5 a, NebTb1 b
WHERE ST_DWithin(point_pos,net_geom ,5e-05)
and b.ogc_fid =500;
The query return immediately and the Explain looks a lot different...
"Nested Loop (cost=0.00..919.32 rows=1 width=128) (actual
time=2.050..12.916 rows=8 loops=1)"
" 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))"
" -> 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)"
" Index Cond: (ogc_fid = 23546)"
" -> 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)"
" Index Cond: (point_pos && st_expand(b.net_geom, 5e-05::double
precision))"
"Total runtime: 13.002 ms"
Apperantly taking the ogc_fid = 500 out of the St_DWithin did the trick!
Thanks Francois / Hugues !? (I'm kind of confused which one your firstname
is? ) :)
cheers
Thomas
2012/9/11 Francois Hugues <hugues.francois at irstea.fr>
> Ok ! Give me one more try and after that someone more experienced than me
> should answer your question.
>
> Maybe the problem comes from the sub query into the st_dwithin
>
> Did you try something like that ?
>
> SELECT ST_AsBinary(point_pos) AS point_pos, a.*
> FROM PointTbl_v5 a, NebTb1
> WHERE ST_DWithin(point_pos,net_geom ,5e-05)
> and NetTbl where ogc_fid =500;
>
> Hugues.
>
>
> -------- Message d'origine--------
> De: postgis-users-bounces at postgis.refractions.net de la part de Thomas
> Klemmer
> Date: mar. 11/09/2012 13:01
> À: PostGIS Users Discussion
> Objet : Re: [postgis-users] ST_DWithin is not using SpatialIndex
> withSubquery
>
> Hi hugues,
>
> 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).
>
> 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.
>
> TK
>
> 2012/9/11 Francois Hugues <hugues.francois at irstea.fr>
>
> > **
> > 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
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 6323 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120912/fb71d6d5/attachment.bin>
More information about the postgis-users
mailing list