[postgis-users] ST_equals not using index ?

[Arnaud Lesauvage]

Oh OK, I see.
So ST_Equals not using the index was a mistake, right ?
I was afraid my logic was failing...

So in 1.4 ~= means ST_Equals with index, and in 1.5 it means BBox are equal.
I just checked both documentations (1.4 and 1.5), and indeed it never 
occured to me that ~= was the only operator that was working on 
geometries rather than on their bounding boxes. I never use operators 
actually (but for && when I have to force the use of the index).
In 1.5, = and ~= seem exactly identical though.

Thanks for the clarification !
Best regards
Arnaud Lesauvage

Le 13/08/2010 14:50, Nicklas Avén a écrit :
> Hallo
>
> This is a little messy story.
>
> As I know it, some one correct me if I'm wrong.
>
> In PostGIS 1.4 ST_Equals don't uses index but then you can instead use
> ~= that uses index
>
> But in PostGIS 1.5 the ~= operator has changed meaning and now only
> compares bounding boxes (have to do with avoiding rechecking for
> operators). So two different geometries with the same bounding box will
> return true.
>
> Instead ST_Equals uses index in PostGIS 1.5
>
> HTH
>
> Nicklas
>
>
> On Fri, 2010-08-13 at 14:15 +0200, Arnaud Lesauvage wrote:
>>  Hi list !
>>
>>  Using Postgis 1.4.1 on PostgreSQL 8.4.3 here (Win32).
>>  I just realized that ST_Equals did not use the GIST index on the
>>  geometry column.
>>  Is this normal ? What is the reason for this ?
>>
>>  I used it to check for duplicate geometries in a table, and whereas
>>  SELECT * FROM mytable t1 WHERE EXISTS
>>  	(SELECT 1 FROM mytable t2 WHERE t1.pkey != t2.pkey
>>  	AND ST_Equals(t1.thegeom,t2.thegeom));
>>  did not use the index on "thegeom",
>>  SELECT * FROM mytable t1 WHERE EXISTS
>>  	(SELECT 1 FROM mytable t2 WHERE t1.pkey != t2.pkey
>>  	AND ST_Equals(t1.thegeom,t2.thegeom)
>>  	AND t1.thegeom&&  t2.thegeom);
>>  did.
>>  I had to stop the first query after it ran for 1hour and "explain" it to
>>  realize this. The second query ran in 25s.
>>
>>  Thanks a lot !
>>
>>  Best regards,
>>  Arnaud Lesauvage
>>  _______________________________________________
>>  postgis-users mailing list
>>  postgis-users at postgis.refractions.net
>>  http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users