What happens if you simplify the query. E.g. just using only geometry in
the where clause, or when you execute separately the subqueries inside
the where clause? Do these already take long to execute, do they use the
indexes defined (tables are recently vacuum'ed?) and do they give back a
sufficiently small number of ids to be selective?
M.
On 16-12-16 14:09, Arjen Haayman wrote:
Hi,
I’ve got this database that has photos that have a location and a
table with key/value metadata. There are almost 450,000 photos and 5
million rows of metadata.
Searching on metadata alone always works, but when I add a spatial
search to it the query freezes if the spatial component is too
precise, i.e. if I search at a certain point or on a bounding box that
is too small. My workaround now is to always extend to a bounding box
with a minimum size.
I cannot find a way how to tackle this. EXPLAIN ANALYZE fails just as
miserably as the original query. I’ve tried all kinds of indexes, but
nothing works.
The queries have been running successfully for years but at a certain
point in time the database got too big apparently and now this happens.
What I really don’t understand why it would fail when the query gets
too specific, I mean searching on a point should be easier than
searching a large bounding box. It usually is the other way around??
Here’s an example query:
SELECT
"id",
"filename",
ST_AsText("geometry") AS "geometry",
ST_AsText("center") AS "center",
"angle"
FROM "photo"
WHERE (ST_Intersects("geometry", st_GeomFromText(
'POINT(4.5063099203616 51.923602970634)', 4326)))
AND ("id" IN (
SELECT "photoId" FROM "photoMetadata"
WHERE ("photoId" IN (
SELECT DISTINCT "photoId" FROM "photoMetadata"
WHERE ("value" = 'KADASTER') AND ("key" = 'source')))
AND ("photoId" IN (
SELECT DISTINCT "photoId" FROM "photoMetadata"
WHERE (key = 'year' AND ( cast(value as int ) >= 1866 AND cast ( value
as int ) <= 1981 ))))))
ORDER BY "filename" LIMIT 36
So this fails. This means that it takes way too long. And a few of
these queries running at the same time completely clogs my machine.
When I change it to something like (ST_Intersects("geometry",
st_GeomFromText( 'POLYGON((6.1444640338619
52.265808403464,6.1444640338619 52.281808403464,6.1496640338619
52.281808403464,6.1496640338619 52.265808403464,6.1444640338619
52.265808403464))', 4326)))
where the extent of the geometry needs to be large enough.
Does anyone have any clues how to tackle this?
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/postgis-users
