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,
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?