CODEWITHSUNDEEP
postgresqlgeospatialpostgis
Sven
Sven

Reputation: 29

"Sparse" Geospatial Queries with ST_Contains are Much Slower than dense ones

I am hitting a wall when it comes to trying to explain what is happening with a query I have. For simplicity, I have stripped it down to the minimum. Simply put, I am try to find all points within a simple envelope like so:

SELECT
    lines.start_point AS point
FROM
    "objects"
    INNER JOIN "lines" ON "lines"."id" = "objects"."line_id"
WHERE
    (ST_Contains
      (
        ST_MakeEnvelope (142.055256,-10.798657,142.385532,-10.485534, 4326),lines.start_point::geometry
      )
    )
LIMIT 50 OFFSET 0;

I have indexes setup on lines.start_point and everything is very fast in areas with lots of data. I get data returned in the sub 500ms range for areas that have lots of data.

What I did not expect is that areas with very little data would be super slow - sometimes > 90,000ms. Is there something I am totally missing here with ST_Contains that would explain this?

As with the example bounding box above and screenshot of return points my data only has 63 start points within this box but the query took 2min 54sec to find them. My only thought is that maybe ST_Contains is quite fast when it can just pick up points quickly when it can find a lot in a single pass but if it has to scan the entire area, it is really slow.

Additionally, I have tried using other ways to looks for points - like the && operator. When this is the case, the roles reverse. Dense areas take a really long time and sparse areas are lightning fast. And example of that query is here:

SELECT
    lines.start_point AS point
FROM
    "objects"
    INNER JOIN "lines" ON "lines"."id" = "objects"."line_id"
WHERE
    lines.start_point && ST_MakeEnvelope (142.055256,-10.798657,142.385532,-10.485534, 4326)
LIMIT 50 OFFSET 0;

Any information would help. Thanks

EDIT: Add && query example

Upvotes: 2

Views: 408

Answers (1)

JGH
JGH

Reputation: 17906

Because of the limit clause, the planner may think it is faster not to use the spatial index. You can try to query all rows and then to apply the limit. Make sure to keep the offset 0 to prevent inlining.

SELECT * FROM (
    SELECT
        lines.start_point AS point
    FROM
        "objects"
        INNER JOIN "lines" ON "lines"."id" = "objects"."line_id"
    WHERE
        (ST_Contains
          (
            ST_MakeEnvelope (142.055256,-10.798657,142.385532,-10.485534, 4326),lines.start_point::geometry
          )
        )
    OFFSET 0)
LIMIT 50;

Also I see you do a cast to geometry, so make sure the index is on the geometry too!

Upvotes: 1

Related Questions