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)                                                            |


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, 2018 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. Dec 12, 2018 at 9:15

1 Answer 1



  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. Dec 12, 2018 at 10:51
  • 5
    @JohnPowell SELECT ST_Intersection(a.civilisation, b.civilisation) FROM UK AS a, EU AS b; -> GEOMETRYCOLLECTION EMPTY. PostGIS politics extension.
    – geozelot
    Dec 12, 2018 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. Dec 12, 2018 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...
    – geozelot
    Dec 12, 2018 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. Dec 12, 2018 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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