3

There are several threads about ST_Intersects being slow (like this one). Main advise is to use spatial index. Well I know that and created spatial index beforehand, but the query is slow nevertheless (takes about 2 hours to complete). Result of explain seems to show no sign of spatial index being used.

I want to mark which LineStrings in one table (~3M records) intersects the target Polygon in other (~30k records). The polygon is huge, but not that huge in comparison to the extent of entire dataset and I expect to catch about 100k records from the highway_only_motor table.

The query:

UPDATE highway_only_motor
   SET has_business_value = true
 WHERE ST_Intersects(geom, (SELECT geom FROM admin_boundary WHERE id = 10236))

Explain result:

QUERY PLAN                                                                                         |
---------------------------------------------------------------------------------------------------|
Update on highway_only_motor  (cost=8.30..1241137.48 rows=1431564 width=1782)                      |
  InitPlan 1 (returns $0)                                                                          |
    ->  Index Scan using admin_boundary_pkey on admin_boundary  (cost=0.29..8.30 rows=1 width=6768)|
          Index Cond: (id = 10236)                                                                 |
  ->  Seq Scan on highway_only_motor  (cost=0.00..1241129.18 rows=1431564 width=1782)              |
        Filter: st_intersects(geom, $0)                                                            |

Tables:

CREATE TABLE public.highway_only_motor (
    osm_id int8 NULL,
    geom geometry NULL,
    "name" text NULL,
    highway text NULL,
    "ref" text NULL,
    oneway text NULL,
    id serial NOT NULL,
    city varchar(255) NULL,
    state varchar(255) NULL,
    country varchar(255) NULL,
    has_business_value bool NOT NULL DEFAULT false,
    CONSTRAINT highway_only_motor_pkey PRIMARY KEY (id)
);
CREATE INDEX highway_only_motor_geom_idx ON public.highway_only_motor USING gist (geom);

CREATE TABLE public.admin_boundary (
    osm_id int8 NULL,
    admin_level int4 NULL,
    boundary text NULL,
    "name" text NULL,
    place text NULL,
    population text NULL,
    geom geometry NULL,
    id serial NOT NULL,
    CONSTRAINT admin_boundary_pkey PRIMARY KEY (id)
);
CREATE INDEX admin_boundary_geom_idx ON public.admin_boundary USING gist (geom);
  • have you tried rewriting the query to remove the sub-select? – Mike T Dec 12 '18 at 8:45
  • @MikeT you have a point - this is the root of the issue. With select replaced with straight WKT (a copy-paste) the query takes less than 30 seconds. Though I fail to properly rewrite the query to achieve the same result. – SS_Rebelious Dec 12 '18 at 9:15
7

Try

WITH
  poly AS (
    SELECT geom
    FROM   admin_boundary
    WHERE  id = <id>
  )

UPDATE  highway_only_motor AS a
  SET   has_business_value = true
  FROM  poly AS b
  WHERE ST_Intersects(a.geom, b.geom);

With the pre-selection in the CTE, you should get similar performance as with a direct geometry reference in the ST_Intersects.

  • 1
    This type of thing seems to come up once a week. I have been too immersed in watching my country of origin, the UK, slowly self destruct, from the safe distance of Spain, to have any time to answer questions. – John Powell Dec 12 '18 at 10:51
  • 4
    @JohnPowell SELECT ST_Intersection(a.civilisation, b.civilisation) FROM UK AS a, EU AS b; -> GEOMETRYCOLLECTION EMPTY. PostGIS politics extension. – ThingumaBob Dec 12 '18 at 11:03
  • 3
    Rotfl. Thank you my German friend for making me laugh. But really, based on current polls, it is SELECT ST_Area(ST_Intersection(a.civilisation, b.civilisation)) FROM UK AS a, EU AS b; result 0.53. – John Powell Dec 12 '18 at 11:06
  • 1
    @JohnPowell ...oh there's plenty of opportunity in PostGIS' function list =) those are the polls now? I really should follow the news some more. decision is tonight? ...well, last resort (and excuse my cynicism): it seems that Spain will stay loyal to the UNO migration pact... – ThingumaBob Dec 12 '18 at 11:56
  • 1
    Yup, whatukthinks.org/eu. It is incredible that it is only 53%. No, don't follow politics, it is massively depressing. Postgis and Python are much more enlightening. Yeah, Sanchez is a good PM, but, minority government. There are a lot of crazies here too, though nothing quite like the freakshow in London. – John Powell Dec 12 '18 at 12:02

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.