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

[Andy Colson]

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.com
> http://about.me/marcello.benigno
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>