-
Type:
Improvement
-
Resolution: Fixed
-
Priority:
Major
-
Component/s: Cloud Integrations
-
None
The solardin.claim_datum_stream_poll_task() database function is used to "claim" a Cloud Datum Stream Poll Task for execution. This function queries theĀ solardin.cin_datum_stream_poll_task table like this:
SELECT * FROM solardin.cin_datum_stream_poll_task WHERE status = 'q' AND exec_at <= CURRENT_TIMESTAMP LIMIT 1
There is an index already defined that looks like it was meant to be used by that query:
CREATE INDEX cin_datum_stream_poll_task_exec_idx ON solardin.cin_datum_stream_poll_task (exec_at DESC) INCLUDE (status);
However the index is not actually used, when testing with EXPLAIN. In addition, the query does a SELECT * so including the status column alone in the index via the INCLUDE (status) statement is not actually of any benefit there.
For the best query access time, perhaps a different, partial index like the following would be better:
CREATE INDEX cin_datum_stream_poll_task_exec_idx ON solardin.cin_datum_stream_poll_task (exec_at) WHERE (status = 'q');
and then get the index in use by tweaking the claim query to add an ORDER BY exec_at DESC like this:
SELECT * FROM solardin.cin_datum_stream_poll_task WHERE status = 'q' AND exec_at <= CURRENT_TIMESTAMP ORDER BY exec_at LIMIT 1