[postgis-users] ST_CoveredBy() query run very very very slow

[Pietro Rossin]

Hello everybody
I'm trying to select features from two spatial tables, geometry 2D.

The first table is polylines from my whole area (984000 records).
The second table is a polygon table, 970000 records. These polygons were
created from the first table with an external tool (FME).

Lots of polylines weren't converted to polygons and now I want to extract
(into a new table) from the first table all the lines not converted to
polygon to perform additional operations.

The two table have indexes (gist) on the geom column

So, my query is:

SELECT a.id, a.layer, a.the_geom into newtable
  FROM my_first_table as a, my_second_sable as b
  where
  not ST_CoveredBy(a.the_geom, b.the_geom);

Yesterdary I let it run for 72.000.000ms and then I had to stop it.
I thought it was because of the Z values of all the geometries and the query
was:

SELECT a.id, a.layer, a.the_geom into newtable
  FROM my_first_table as a, my_second_sable as b
  where
  not ST_CoveredBy(st_force_2d(a.the_geom), st_force_2d(b.the_geom));

Today It's running for 14.000.000ms and I have to stop it again...

Why is this query so slow???
My pc isd Windows XP 32b 4Gb ram, 2 opteron 252 processor.

Thanks
Pietro



--
View this message in context: http://postgis.17.x6.nabble.com/ST-CoveredBy-query-run-very-very-very-slow-tp5003187.html
Sent from the PostGIS - User mailing list archive at Nabble.com.