Best performing query st_intersects()

Question

I am running PostgreSQL 9.4 and PostGIS 2.3

For the background: I developped a DB on a regular machine used as host. I needed to use a view+rule system to allow the work to be done on spatial subsets of the datas. It worked fine, however when my company acquired a proper server, and after I migrated the DB, everything started to be extremely slow, a QGIS project is now taking around 2:30min to load, and the refresh timings are also extremely long. I figure the problem comes from the performances allowed by the server, and since updatable materialized views are not possible, I would like to try and optimize my views query. (actually I am trying also something else: emulate a viewing system with tables and triggers, but it is a rather complex architecture).

I saw in this post PostGIS doesn't use spatial index with ST_Intersects that it is better to not put subqueries inside the st_intersects, which after testing obviously is conclusive. But I face another problem, my view is not only a subset of the raw datas, it also put in shape the foreign keys in order to be readable. So I need to use twice the same table: once to shape the foreign key, and another for the spatial subset. Here is an example of my original query:

SELECT object.id_obj,
    project.name,
    object.geom
   FROM object
     LEFT JOIN project ON project.id_project = object.id_project
  WHERE st_intersects((select geom from project where id_project = 1), obj.geom);

This query takes 123msec, with 61 results.

Here is what I tried:

SELECT object.id_obj,
        project.name,
        object.geom
       FROM object
         LEFT JOIN project ON project.id_project = object.id_project
      WHERE project.id_project = 1 and st_intersects(project.geom, obj.geom);

The query runs much faster, 12msec, but only get 46 results, because sometimes the objects have the same id_project as foreign key, but sometimes not, and the ones which don't are not included in the result.

I obviously see where the problem lies, but I cannot figure out how to solve it. Any idea?

st_intersects() does currently use a bounding box search based on indexes: https://postgis.net/docs/ST_Intersects.html — HeikkiVesanto, Jan 22 '18 at 15:16
Do you really have to work with the real geometries in order to get the correct spatial subset? Because if you can work on the bounding boxes (with the && operator for example), your queries will usually take much less time to execute. This question may help you: https://gis.stackexchange.com/questions/83387/whats-the-fastest-way-to-do-a-bounding-box-query-in-postgis — Mefimefi, Jan 29 '18 at 15:38

Best performing query st_intersects()

0 Answers0