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

[Stephen Woodbridge]

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