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

[Marcello Benigno]

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.**com <http://profmarcello.blogspot.com>
>>> http://about.me/marcello.**benigno <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<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<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<http://lists.osgeo.org/cgi-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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130215/63a9e5ba/attachment.html>