Qfyilyi

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

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.