0

I have trouble optimizing a query for analytics.

Ok so I have bunch of table (lets say, cable, chamber). All this table have a geometry. This geometry can be linestring, point, etc... (all the same srid). All this table also have a FK to a table project. So we can track project on entities.

Now SQL:

CREATE TABLE cable (
   cab_id uuid primary key default uuid_generate_v4(),
   cab_proj uuid reference project (proj_id),
   the_geom geometry(linestring, 4326)
);

CREATE TABLE chamber (
   chb_id uuid primary key default uuid_generate_v4(),
   chb_proj uuid reference project (proj_id),
   the_geom geometry(point, 4326)
);

CREATE TABLE project (
   proj_id uuid primary key default uuid_generate_v4(),
   proj_desc text
);

What I want is a convex hull around project, dynamically updated, so I have created a view :

CREATE VIEW v_project_limits AS 
  SELECT p.proj_id, 
    p.proj_desc,
   COALESCE(
     st_collectionextract(
       st_multi(
         st_union(
           st_convexhull(
             st_collect(
               ARRAY [ cab.the_geom, chb.the_geom ]
             )
           )
          )
         ),
       3), 
       st_multi(st_geomfromtext('POLYGON((0 0, 0 0, 0 0, 0 0))' :: TEXT, 2154))) AS geom,
  FROM project p
    LEFT JOIN cable cab ON (cab.cab_proj=p.proj_id)
    LEFT JOIN chamber chb ON (chb.chb_proj=p.proj_id)
  GROUP BY p.proj_id, p.proj_desc

On my real data I have 6 tables to analyze. This query makes +4s to execute. I tried query planner explainer like http://tatiyants.com/pev/#/plans but didn't get much result...

  • 1
    Throw away the ST_Collect step, it's redundant as the union should aggregate automatically. I'm not sure if passing a collection into the union will hamstring it when it comes time to build out a tree to optimize the union. Finally, you say the query is "slow" but you provide no context on the inputs. What is the count of all records in your joined relation? Because that's what you're asking the system to process. – Paul Ramsey Oct 9 '17 at 19:45
  • st_convexhull cannot be used with an array of geometry, that's why I put a st collect. – Rémi Desgrange Oct 10 '17 at 8:03
  • The amount of data is not relevant. This query is slow by design, an the get slower exponentialy. I present here my solution which is not great and ugly. – Rémi Desgrange Oct 10 '17 at 8:09
  • The amount of data is extremely relevant -- if there are millions of rows then 4s is fast, if there are 10, it is horrible. It would also help to put in the output of an EXPLAIN statement, which will shed light on keys being used (or not), etc. It seems you have no spatial index, for example, from the information so far provided. Also, what are you actually trying to do -- it might be possible to rewrite the order of operations/joins to achieve that. – John Powell Oct 10 '17 at 10:08
  • there are hundred, max thouand of record. I have set index on geometry. I cannot put easily the output of EXPLAIN, sorry, I'll try to change the order of the joins to see if it has any impact – Rémi Desgrange Oct 10 '17 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.