Postgres query running very slow

Question

SELECT st.id
FROM
       station st, strike s
WHERE s.srikedatetime > (current_timestamp - interval '20 seconds')  and srikedatetime < current_timestamp
    AND ST_Distance_Sphere(st.Geometry, s.strikepoint)/1000 <= st.radius

The idea is when a strike hits, it could be in boundary of multiple stations based on station monitoring radius. I need to select all stations affected by any strikes in the last 20 seconds. In 20 seconds I can have thousands of strikes and when executing this query every 20 seconds, the CPU goes high and the query takes minutes to complete. When CPU is not high, it runs in milliseconds.

Query Plan:

"Nested Loop  (cost=0.42..505110.20 rows=21 width=7)"
"  Join Filter: ((_st_distance(geography(a.Geometry), geography(s.strikepoint), 0::double precision, false) / 1000::double precision) <= (a.radius)::double precision)"
"  ->  Index Only Scan using dup_strike_constraint on strike s  (cost=0.42..8.45 rows=1 width=36)"
"        Index Cond: ((srikedatetime > (now() - '00:00:20'::interval)) AND (srikedatetime < now()))"
"  ->  Seq Scan on station  st  (cost=0.00..505084.86 rows=62 width=549)"

I have tried inner join, something like this

Inner JOIN strike s ON ST_Distance(a.Geometry, s.strikepoint) < 1

and also tried ST_DWithin in where clause and grouping still slow.

ST_DWithin(s.strikepoint,a.Geometry, a.radius)

Any thoughts, please? I have indexes on strike and station tables.

The data types for st.strikepoint and a.geomerty columns is geometry. The coordinate system is 4326. Thank You!

Answer 1

You could create a Lazy Materialized View to stock just the data you need. It's a lot faster if you just query one table instead of joining 'station' and 'strike' tables.

Obviously, your lazy table should be indexed properly, especialy if you don't plan to 'clean' the data periodicaly.

Other option could be using a TEMP table to handle the data, if it's not necessary to store it historically and you handle the data in one session. TEMP tables are just visible to one session and 'die' when the session ends. You can create them and destroy them at any point, but in this case you should pay attention to the memory usage.

Hope it helps.