[postgis-users] Query choces on searching too small area

[Arjen Haayman]

Just geometry works just fine. No geometry also. It starts to act up when I use the spatial query in combination with at least 2 metadata queries.
Whether the sub-results are sufficiently small depends entirely on the metadata queried. 

-----Original Message-----
From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Martijn Meijers
Sent: vrijdag 16 december 2016 15:01
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Query choces on searching too small area

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
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users