Improve Cloud Datum Stream Poll Task "claiming" procedure

XMLWordPrintable

    • 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
      

            Assignee:
            Matt Magoffin
            Reporter:
            Matt Magoffin
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved: