I'm creating a new table of trips in my PostgreSQL/PostGIS database, where trips are comprised of a series of individually identified points, based on where the points within each trip are. The idea is to select any trip which has at least one point that is NOT within a certain area. So that means filtering through every point to see if it's in or out of a certain spatial realm, and if any point is outside of the realm, then I put the trip_id of that point (multiple points make up a single trip, and all points within a trip share a trip_id) into this new table. The query is written as follows:
CREATE TABLE main.trips_gps_validity_21 as
SELECT DISTINCT t.trip_id
FROM main.points t, main.sfoutline y
WHERE not(ST_Contains(y.geom, t.geom_met))
Here, y.geom is the spatial polygon that designates my area of interest, and the t.geom_met is the geometry for each point within a trip.
When I run the inverse, i.e. the bottom line is "WHERE ST_Contains(y.geom, t.geom_met)" instead of "WHERE NOT", the spatial index is used. When the "NOT" is applied, the spatial index is ignored. Why would this be happening? Running the "NOT" query with a "limit 10" addendum takes 2.953 seconds, while the query without "NOT" takes only 90 msecs, so it seems like an index would help speed things along.
ST_Disjoint
where t.trip_id not in.. [fast ST_Contains query]
. But if ST_Contains finds just a handful of features and NOT ST_Contains much more the difference in speed may be normal.