[postgis-users] GIST index speed

Obe, Regina robe.dnd at cityofboston.gov
Thu Jun 5 04:07:40 PDT 2008


Well this is just a guess on my part.  ST_Within() is the standards
compliant named one.  Within() is obsolete by standards as well (even
though you are right most spatial databases never needed that extra
annoying && be added).

The reason I believe Within() was not made to behave exactly like
ST_Within() is for reverse-compatibility.


b && a AND ST_Within(a,b)  is slightly slower than 

b && a AND Within(a,b) 

Because the postgres optimizer does not make the distinction that 
a && b is the same as b && a  --> although it would realize that a && b
AND a && b -> a && b

so lets say you had code like this

b && a AND Within(a,b)

and the behavior of Within was changed to  a && b AND _ST_Within(a,b)

Then your old code would suddenly expand to 

b && a AND a && b AND _ST_Within(a,b)

Which is slower than what you had before.

I asked this on the PostgreSQL general list before that why doesn't
Postgres use the 
commutator property of an operator to collapse b && a and a && b -> a &&

Tom Lane replied and he said from a planner standpoint, the extra cycles
it would add to planning would be more than the gain in most cases.

There are similar things about the new function cost thing I find
annoying and don't behave as intuitively as I would like for the same

Hope that helps,

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Markus Schaber
Sent: Thursday, June 05, 2008 5:40 AM
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] GIST index speed

Hi, Kevin,

Kevin Neufeld <kneufeld at refractions.net> wrote:

> Are you using within() or ST_Within()? The latter automatically uses
> gist index, the former does not.

As I was absent from the lists for some time, why was this distinction

Normally, I would expect the standards compliant version to be
optimizable by the query planner.


Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
postgis-users mailing list
postgis-users at postgis.refractions.net
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

More information about the postgis-users mailing list