2

I need to get the outer polygon of the LSOAs within 10 miles of each postcode centroid. There are 180k LSOA polygons in table1 and 2.6 million postcode centroid points in table2.

I have created 2 geography columns (polygon.geog, pois.geog) and one geometry column polygon.coordinates. All of them are indexed using GIST.

I tried running for 10 using the code below which took 1 minute.

SELECT pois.objectid, ST_Multi(ST_Union(polygon.coordinates)) as coords   
              into table3
              FROM table1 as polygon 
              INNER JOIN table2 as pois 
              ON ST_DWithin(polygon.geog,pois.geog, 10* 1609.34,false)
              group by pois.objectid
            limit 10;

Query plan, enter image description here

[
  {
    "Plan": {
      "Startup Cost": 0.84,
      "Plans": [
        {
          "Partial Mode": "Simple",
          "Startup Cost": 0.84,
          "Plans": [
            {
              "Startup Cost": 0.84,
              "Plans": [
                {
                  "Startup Cost": 0.43,
                  "Scan Direction": "Forward",
                  "Plan Width": 39,
                  "Node Type": "Index Scan",
                  "Plan Rows": 3011500,
                  "Relation Name": "table2",
                  "Alias": "pois",
                  "Parallel Aware": false,
                  "Parent Relationship": "Outer",
                  "Total Cost": 11352214.75,
                  "Index Name": "postcode_pkey"
                },
                {
                  "Filter": "(((pois.geog)::geography && _st_expand(geog, '16093.4'::double precision)) AND _st_dwithin(geog, (pois.geog)::geography, '16093.4'::double precision, false))",
                  "Startup Cost": 0.41,
                  "Scan Direction": "NoMovement",
                  "Plan Width": 11560,
                  "Node Type": "Index Scan",
                  "Index Cond": "(geog && _st_expand((pois.geog)::geography, '16093.4'::double precision))",
                  "Plan Rows": 1,
                  "Relation Name": "table1",
                  "Alias": "polygon",
                  "Parallel Aware": false,
                  "Parent Relationship": "Inner",
                  "Total Cost": 78.31,
                  "Index Name": "gidgeo"
                }
              ],
              "Node Type": "Nested Loop",
              "Plan Rows": 181100,
              "Join Type": "Inner",
              "Parallel Aware": false,
              "Parent Relationship": "Outer",
              "Plan Width": 5783,
              "Total Cost": 247219664.75
            }
          ],
          "Node Type": "Aggregate",
          "Strategy": "Sorted",
          "Plan Rows": 181100,
          "Parallel Aware": false,
          "Group Key": [
            "pois.objectid"
          ],
          "Parent Relationship": "Outer",
          "Plan Width": 39,
          "Total Cost": 247223286.75
        }
      ],
      "Node Type": "Limit",
      "Plan Rows": 1,
      "Parallel Aware": false,
      "Plan Width": 39,
      "Total Cost": 1365.96
    }
  }
]

Is it possible to improve the speed of this code?

  • what does explain show? – Ian Turton Mar 29 '18 at 12:21
  • 3
    Running 2.6M unions will take a while, no way around it. You're really trying to create an output table with 2.6M polygons? If your data are not truly global, you could move to a projected system and work in geometry for the dwithin, but you'd still be doing 2.6M unions, so that would be a small improvement, not a huge change. – Paul Ramsey Mar 29 '18 at 12:29
  • 1
    Add "polygon.geog && pois.geog and" before ST_DWithin to force index use. Please remember to always include the query plan in the body of all SQL tuning questions. – Vince Mar 29 '18 at 13:02
  • 4
    Actually, don't do this ▲. That's not an appropriate index operator for this case, and the correct operator is included automatically. – dbaston Mar 29 '18 at 13:48
  • 1
    Doh, yeah. It's ST_Within that doesn't use the index, not st_Dwithin. We really do need the EXPLAIN plan to help you. EDIT the question. – Vince Mar 29 '18 at 14:40

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.