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

[Rémi Cura]

Hey, there is a dedicated "slow query" protocol on postgres user list, and
its quite sane.
For instance, it would suggest you to give the version number you use, your
hardware, etc etc.

About you query, I guess  your photo are geotagged (i.e. each photo is a
point, and maybe you have a precision attribute).
Using ST_Intersects thus could be replaced by ST_DWithin with your
precision / default tolerancey, which is safer.

Index not kicking may have many causes, such as outdated statistics (have
you vacuum analyse -ed often?), wrong config regarding your hardware
(seq_page_cost,
and so),
poorly written query, etc.

Anyway your query should not freeze on only 500k geometries, so I'm also
guessing that in the table "photo" you not only store photo point /
geometry, but also the binary of the photo, which is bound to be dangerous.

So steps to fix your problem
 - update postgres / postgis if you can
 - check stats / vacuum
 - check postgres config
 - rewrite your query for a better form (see example 1 )
 - post on list, this might be a bug
 - rewrite query to force to perform first geometry test then the other
(example query 2)
 - create a "proxy" photo_proxy table that contains only photo_id and
photo.geometry
 - force use of index via settings (usually a very bad idea)
 - ...


Here is how your query could be simplified :

SELECT

"id",

"filename",

ST_AsText("geometry") AS "geometry",

ST_AsText("center") AS "center",

"angle"

FROM "photo"

WHERE (*ST_DWithin*("geometry", st_GeomFromText( 'POINT(4.5063099203616
51.923602970634)', 4326)*,your_precision*))

AND "id" 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


here is the query to force use of geometric index :

WITH photos_spatialy_close AS (
  SELECT id AS photoId
  FROM photo
  WHERE *ST_DWithin*("geometry", st_GeomFromText( 'POINT(4.5063099203616
51.923602970634)', 4326)*,your_precision*)
  LIMIT 36
)
, photo_with_correct_metatadata AS (

  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))
)
, keeping_photo_id_in_both_set AS (
  SELECT photoId
   FROM photos_spatialy_close
  INTERSECTS
  SELECT photoId
  FROM photo_with_correct_metatadata
)
SELECT

"id",

"filename",

ST_AsText("geometry") AS "geometry",

ST_AsText("center") AS "center",

"angle"

FROM keeping_photo_id_in_both_set LEFT OUTER JOIN photo ON ( photoId = id)

LIMIT ...




Cheers
Remi-C

2016-12-16 17:48 GMT+01:00 Sandro Santilli <strk at kbt.io>:

> On Fri, Dec 16, 2016 at 03:33:32PM +0100, Arjen Haayman wrote:
> > QUERY PLAN
>
> [...]
>
> > What does this tell you?
>
> That your query is too complex ?
> Check out http://explain.depesz.com
>
> --strk;
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161216/6501c814/attachment.html>