<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 9.00.8112.16457"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=781502511-15022013><FONT color=#0000ff
size=2 face=Arial>Marcello, </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=781502511-15022013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=781502511-15022013><FONT color=#0000ff
size=2 face=Arial>Then both would use an index.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=781502511-15022013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=781502511-15022013><FONT color=#0000ff
size=2 face=Arial>Note I WOULD expect your ST_Intersects to be faster, because
you are doing an expensive ST_Intersection.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=781502511-15022013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=781502511-15022013><FONT color=#0000ff
size=2 face=Arial>&& 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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=781502511-15022013><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=781502511-15022013><FONT color=#0000ff
size=2 face=Arial>Try to do a simple COUNT(*) and your && would probably
be faster but return a bigger number</FONT></SPAN></DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <B>On Behalf Of </B>Marcello
Benigno<BR><B>Sent:</B> Friday, February 15, 2013 6:16 AM<BR><B>To:</B> PostGIS
Users Discussion<BR><B>Subject:</B> Re: [postgis-users] Query without index
faster than query with index (using GIST)<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV>Hi Steve,</DIV>
<DIV><BR></DIV>I'm using this version:
<DIV><BR></DIV>
<DIV>"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""</DIV>
<DIV><BR></DIV>
<DIV>in Ubuntu 12.10.</DIV>
<DIV><BR></DIV>
<DIV>Thanks for the answers.<BR><BR>
<DIV class=gmail_quote>2013/2/14 Stephen Woodbridge <SPAN dir=ltr><<A
href="mailto:woodbri@swoodbridge.com"
target=_blank>woodbri@swoodbridge.com</A>></SPAN><BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>In addition you need to tell us what version of software you
are running.<BR><BR>select postgis_full_version();<BR><BR>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.<BR><BR>-Steve W
<DIV class=HOEnZb>
<DIV class=h5><BR><BR>On 2/14/2013 7:06 PM, Andy Colson wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>Try running the same query three or four times in a row.
I'll bet you<BR>get a different time for each of them.<BR><BR>Your
numbers are way to close together to say that one is faster than<BR>the
other.<BR><BR>-Andy<BR><BR><BR>On 02/14/2013 05:36 PM, Marcello Benigno
wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>Hello everybody,<BR><BR>I wonder why this is happening,
all tables are with indexes and has<BR>already run a vacuum analyze to all
of them:<BR><BR>SELECT
ms_ucs,<BR>SUM(ST_Area(Geography((ST_<U></U>Intersection(analises.pol_<U></U>geom,ms_2010_s1.geom)))))/<U></U>10000<BR><BR>AS
area_ha<BR>FROM analises, ms_2010_s1<BR>WHERE analises.pol_geom &&
ms_2010_s1.geom<BR>AND
ST_Intersects(analises.pol_<U></U>geom,ms_2010_s1.geom)<BR>GROUP BY
ms_ucs; *-- 889 ms*<BR><BR>SELECT
ms_ucs,<BR>SUM(ST_Area(Geography((ST_<U></U>Intersection(analises.pol_<U></U>geom,ms_2010_s1.geom)))))/<U></U>10000<BR><BR>AS
area_ha<BR>FROM analises, ms_2010_s1<BR>WHERE
ST_Intersects(analises.pol_<U></U>geom,ms_2010_s1.geom)<BR>GROUP BY
ms_ucs; *-- 870 ms !!!*<BR><BR>Please note that the second, without using
the && operator was faster,<BR>there's something wrong or I'm not
understanding the functioning of<BR>the index?<BR><BR>Thanks in
advance,<BR>--<BR>*Marcello Benigno B. de Barros Filho*<BR>Prof. do Curso
Superior de Tecnologia em Geoprocessamento - IFPB<BR>Mestre em Ciências
Geodésicas e Tecnologias da Geoinformação - UFPE<BR>Doutorando em
Tecnologia Ambiental e Recursos Hídricos - UFPE<BR><A
href="http://profmarcello.blogspot.com"
target=_blank>http://profmarcello.blogspot.<U></U>com</A><BR><A
href="http://about.me/marcello.benigno"
target=_blank>http://about.me/marcello.<U></U>benigno</A><BR><BR><BR>______________________________<U></U>_________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@lists.osgeo.org"
target=_blank>postgis-users@lists.osgeo.org</A><BR><A
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target=_blank>http://lists.osgeo.org/cgi-<U></U>bin/mailman/listinfo/postgis-<U></U>users</A><BR><BR></BLOCKQUOTE><BR>______________________________<U></U>_________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@lists.osgeo.org"
target=_blank>postgis-users@lists.osgeo.org</A><BR><A
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target=_blank>http://lists.osgeo.org/cgi-<U></U>bin/mailman/listinfo/postgis-<U></U>users</A><BR></BLOCKQUOTE><BR>______________________________<U></U>_________________<BR>postgis-users
mailing list<BR><A href="mailto:postgis-users@lists.osgeo.org"
target=_blank>postgis-users@lists.osgeo.org</A><BR><A
href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users"
target=_blank>http://lists.osgeo.org/cgi-<U></U>bin/mailman/listinfo/postgis-<U></U>users</A><BR></DIV></DIV></BLOCKQUOTE></DIV><BR><BR
clear=all>
<DIV><BR></DIV>-- <BR><B>Marcello Benigno B. de Barros Filho</B><BR><FONT
size=1>Prof. do Curso Superior de Tecnologia em Geoprocessamento -
IFPB<BR>Mestre em Ciências Geodésicas e Tecnologias da Geoinformação -
UFPE</FONT>
<DIV><FONT size=1>Doutorando em Tecnologia Ambiental e Recursos Hídricos -
UFPE</FONT><FONT size=1><BR></FONT><FONT size=1><A
href="http://profmarcello.blogspot.com"
target=_blank>http://profmarcello.blogspot.com</A></FONT>
<DIV><A href="http://about.me/marcello.benigno" target=_blank><FONT
size=1>http://about.me/marcello.benigno</FONT></A></DIV></DIV></DIV></BODY></HTML>