[postgis-users] Query without index faster than query with index (using GIST)

[Paragon Corporation]

Marcello, 
 
Then both would use an index.
 
Note I WOULD expect your ST_Intersects to be faster, because you are doing
an expensive ST_Intersection.
 
&& would return all that intersect the bounding box, but ST_Intersection
might turn up with empties that don't intersect so your && ends up doing
more work in the ST_Intersection call.
 
Try to do a simple COUNT(*) and your && would probably be faster but return
a bigger number

  _____  

From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Marcello Benigno
Sent: Friday, February 15, 2013 6:16 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Query without index faster than query with
index (using GIST)


Hi Steve,

I'm using this version: 

"POSTGIS="2.0.1 r9979" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March
2012" LIBXML="2.8.0""

in Ubuntu 12.10.

Thanks for the answers.


2013/2/14 Stephen Woodbridge <woodbri at swoodbridge.com>


In addition you need to tell us what version of software you are running.

select postgis_full_version();

In the newer versions many of the ST_* function automatically do and
appropriate && under the covers so adding it in does not improve anything,
but dos make you query backwards compatible with older versions.

-Steve W 


On 2/14/2013 7:06 PM, Andy Colson wrote:


Try running the same query three or four times in a row.  I'll bet you
get a different time for each of them.

Your numbers are way to close together to say that one is faster than
the other.

-Andy


On 02/14/2013 05:36 PM, Marcello Benigno wrote:


Hello everybody,

I wonder why this is happening, all tables are with indexes and has
already run a vacuum analyze to all of them:

SELECT ms_ucs,
SUM(ST_Area(Geography((ST_Intersection(analises.pol_geom,ms_2010_s1.geom))))
)/10000

AS area_ha
FROM analises, ms_2010_s1
WHERE analises.pol_geom && ms_2010_s1.geom
AND ST_Intersects(analises.pol_geom,ms_2010_s1.geom)
GROUP BY ms_ucs; *-- 889 ms*

SELECT ms_ucs,
SUM(ST_Area(Geography((ST_Intersection(analises.pol_geom,ms_2010_s1.geom))))
)/10000

AS area_ha
FROM analises, ms_2010_s1
WHERE ST_Intersects(analises.pol_geom,ms_2010_s1.geom)
GROUP BY ms_ucs; *-- 870 ms !!!*

Please note that the second, without using the && operator was faster,
there's something wrong or I'm not understanding the functioning of
the index?

Thanks in advance,
--
*Marcello Benigno B. de Barros Filho*
Prof. do Curso Superior de Tecnologia em Geoprocessamento - IFPB
Mestre em Ciências Geodésicas e Tecnologias da Geoinformação - UFPE
Doutorando em Tecnologia Ambiental e Recursos Hídricos - UFPE
http://profmarcello.blogspot. <http://profmarcello.blogspot.com> com
http://about.me/marcello. <http://about.me/marcello.benigno> benigno


_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-
<http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
bin/mailman/listinfo/postgis-users




_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-
<http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
bin/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-
<http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users>
bin/mailman/listinfo/postgis-users





-- 
Marcello Benigno B. de Barros Filho
Prof. do Curso Superior de Tecnologia em Geoprocessamento - IFPB
Mestre em Ciências Geodésicas e Tecnologias da Geoinformação - UFPE 
Doutorando em Tecnologia Ambiental e Recursos Hídricos - UFPE
http://profmarcello.blogspot.com 
 <http://about.me/marcello.benigno> http://about.me/marcello.benigno
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130215/7b589fbb/attachment.html>