[postgis-users] ST_equals not using index ?

[Nicklas Avén]

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
>