Why do the following PostGIS operators perform so differently? [duplicate]

Question

I encountered large performance differences for spatial intersections in PostGIS depending on the operator I'm using and I can't figure out what's causing them.

Background:

I have a large set of points and I'm usually selecting a subset of them based on a polygon outline. Something like st_intersects (polygon, points) works for this, but there is also the && operator. Looking at the st_intersects and && documentation, they seem to do, at least in the case of points and polygons, the same thing. I would even have imagined && to be a bit slower, since it constructs bounding boxes around the geometries to compare.

However, when optimizing my query, I used EXPlAIN to estimate the costs of different approaches and discovered that && tends to be much cheaper (factor ~10). Can someone please explain to me what I'm missing here?

I made two small examples in PostGIS 2.5.0 to try it out. Note that they only differ in the one line where the comparison takes place. The rest is just random point generation.

EXPLAIN

WITH x AS
    (select generate_series (1,1000),
     st_setSRID(st_makepoint(
        (random()*360)::float - 180,
        (random()*180)::float - 90),
     4326) geom)

SELECT * FROM x
WHERE
st_intersects
(geom, st_setSRID(st_geometryfromtext('POLYGON((-74.1 83.8, -13.0 83.8, -13.0 59.6, -74.1 59.6, -74.1 83.8))'), 4326));

CTE Scan on x (cost=5.04..277.54 rows=67 width=36)

EXPLAIN

WITH x AS
    (select generate_series (1,1000),
     st_setSRID(st_makepoint(
        (random()*360)::float - 180,
        (random()*180)::float - 90),
     4326) geom)

SELECT * FROM x
WHERE
(geom && st_setSRID(st_geometryfromtext('POLYGON((-74.1 83.8, -13.0 83.8, -13.0 59.6, -74.1 59.6, -74.1 83.8))'), 4326));

CTE Scan on x (cost=5.04..27.54 rows=200 width=36)

Answer 1

First thing to note is that the results from ST_Intersects are different (or can be different) than the result of the && operator, as the && operator only compares the geometries' MBR (Minimum bounding rectangle) and the ST_Intersects compares the "real" geometries of the objects.

To do the && operator comparison, the database engine only as to consult the spatial index of the table (Spatial Indexes are based on the MBRs of the indexed objects).

In the case of the ST_Intersects comparison it first consults the spatial index, to filter out objects whose MBRs don't intersect, and then computes the spatial relation between the "real" geometries in order to see if they actually intersect.

Answer 2

They don't do the same.

&& is fast but inaccurate, while st_intersects is exact. One may use && to identify potential match, and refine using st_intersects

WITH src AS (
select st_geomFromText('LINESTRING(0 0, 3 3)') as l,
       st_geomFromText('POINT(0 1)') as pt
)
SELECT st_intersects(l,pt), l && pt as "&&"
from src;

 st_intersects | &&
---------------+----
 f             | t