Determine if approximate count of features in polygons is less than 100_000

Question

Using PostgreSQL 12.

I have a table of polygons, a table of points, and a table of lines. All tables have GIST indexes on the geometry column. There is a view that includes both the lines and points, view_points_and_lines.

There is a script that I run on these that is fairly intensive, and I do not want to run it if there are more than 100_000 total features (points and lines) in the targeted polygons (combined).

Thus, before I run the "intensive query", I would like to determine whether the total count of the features in the targeted polygons is < 100_000. Note that if a feature is included in multiple polygons, it should be counted multiple times.

However, running a query like

SELECT COUNT(tf.*) FROM view_points_and_lines tf JOIN polygons sf ON ST_Intersects(tf.geom, sf.geom)

can be very slow itself, especially if there many polygons and much more than 100_000 points.

I have also tried returning a Boolean like:

SELECT COUNT(tf.*) < 100000 FROM view_points_and_lines tf JOIN polygons sf ON ST_Intersects(tf.geom, sf.geom)

but that does not seem to speed up the query, and the query plan is the same.

Given that I really only need a Boolean value, and the count can be approximate, is there any way to speed up this query?

Answer 1

Update as per comment:

To count the total amount of intersections with any of of the polygons, run a similar boolean COUNT condition like yours on both tables independently (as per the last paragraph below), and SUM them up:

SELECT SUM(_cnt) < 100000
FROM   (
    SELECT COUNT(e.*) AS _cnt
    FROM   polygons AS ply
    JOIN   <points> AS e
      ON   ply.geom && e.geom AND ST_Intersects(ply.geom, e.geom)
    UNION ALL
    SELECT COUNT(e.*) AS _cnt
    FROM   polygons AS ply
    JOIN   <lines> AS e
      ON   ply.geom && e.geom AND ST_Intersects(ply.geom, e.geom)
) q
;

---

Old answer:

You'd need a GROUP BY with accompanying HAVING filter to retrieve only those polygons (by <id> column) with less than or equal to 100.000 intersecting features

SELECT sf.<id>
FROM   view_points_and_lines AS tf
JOIN   polygons AS sf
  ON   ST_Intersects(tf.geom, sf.geom)
GROUP BY
       1
HAVING COUNT(tf.*) <= 100000
;

However, it is very likely that the unioned relations (in your View) are the key issue here; due to the union of multiple relations, the planner supposedly resolves to using the index on the polygons table to gather the intersection counts. Instead, you want each of the implicitly involved joined relations being index scanned.

Try

SELECT id
FROM   (
    SELECT ply.<id> AS id
           COUNT(e.*) AS cnt
    FROM   polygons AS ply
    JOIN   <points> AS e
      ON   ST_Intersects(ply.geom, e.geom)
    GROUP BY
           1
    UNION ALL
    SELECT ply.<id> AS id,
           COUNT(e.*) AS cnt
    FROM   polygons AS ply
    JOIN   <lines> AS e
      ON   ST_Intersects(ply.geom, e.geom)
    GROUP BY
           1
) q
GROUP BY
       id
HAVING SUM(cnt) <= 100000
;