[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-users
Subject:    Re: [postgis-users] seq scan for spatial operator
From:       Paul Ramsey <pramsey () refractions ! net>
Date:       2005-06-21 17:17:32
Message-ID: 42B84BAC.2010109 () refractions ! net
[Download RAW message or body]

That is by design, that is how indexes work in PostgreSQL. Only SQL that 
uses an operator (in your case, &&) will access the index. Your first 
query does not have an operator, it has only a function(). Spatial SQL 
should always look (generally) like this:

SELECT <various things>
FROM <various table>
WHERE
   <an indexed && test>
AND
   <an exact function() test>

Xavier Vanderstukken wrote:

> The following query performs seq scan on all the table (600.000row for 
> 1matched result).
> 
> select * from foo where equals (asText(foo.geom),'POLYGON ((4.3596 
> 50.51121,4.3597584 50.51127,4.35994 50.511333,4.3601313 
> 50.511406,4.360355 50.511482,4.35968 50.511448,4.3596 50.51121))')
> 
> -->30sec response time
> 
> but if I add and intersect request :
> foo.geom&& GeometryFromText('.............',-1)
> 
> -->response time 50ms no seq scan
> 
> _________________________________________________________________
> Protégez votre boîte de réception: Phishing : comment l'identifier, le 
> signaler et l'empêcher   http://www.fr.msn.be/security/phishing/
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users


[prev in list] [next in list] [prev in thread] [next in thread]