Everything here works fine - but after a handful of product iterations &
production adjustments, a query that handles a "task queue" across a few tables
looks a bit ugly.
I'm wondering if anyone can see obvious improvements.
There are 3 tables:
upstream_provider
task
task_queue
Originally we needed to select 50 items off the top of the queue at a time.
Then we needed to set a max of 5 tasks per upstream provider (It's a cheap way
to handle throttling).
The table is quite large, so a limit of the last 1000 items drastically
improved performance.
The query got ugly when we needed to add a "priority" toggle to the queue --
basically to mark things as "process ASAP".
The only way I could figure out how to do that, was to add a sort -- on
"is_priority DESC NULLS LAST".
My concern is that the sort needs to happen 3x --
in the subselect for 1000 items
in the partition for row numbering
in the final sort
If anyone has a moment to look this over and suggest anything, I'd be very
thankful. A working reproduction is below.
SQL---
--------------
CREATE TABLE upstream_provider (id SERIAL PRIMARY KEY,
name VARCHAR(32),
is_paused BOOLEAN DEFAULT NULL
);
CREATE TABLE task (id SERIAL PRIMARY KEY,
upstream_provider_id INT NOT NULL REFERENCES
upstream_provider(id),
name VARCHAR(32)
);
CREATE TABLE task_queue (id SERIAL PRIMARY KEY,
task_id INT NOT NULL REFERENCES task(id),
upstream_provider_id INT NOT NULL REFERENCES
upstream_provider(id), # only here because it eliminates expensive joins
elsewhere
processing_status BOOLEAN DEFAULT NULL,
is_priority BOOLEAN DEFAULT NULL
);
SELECT partition1.*
, task.*
FROM (SELECT window1.*
, row_number() OVER (PARTITION BY window1.upstream_provider_id
ORDER BY window1.is_priority DESC NULLS LAST,
window1.task_queue_id
) AS rownum
FROM (SELECT qu.id AS task_queue_id
, qu.upstream_provider_id
, qu.task_id
, qu.is_priority
FROM
task_queue qu
JOIN
upstream_provider ON qu.upstream_provider_id =
upstream_provider.id
WHERE (qu.processing_status IS NULL)
AND (upstream_provider.is_paused IS NOT TRUE)
ORDER BY is_priority DESC NULLS LAST,
qu.id DESC
LIMIT 1000
) window1
) partition1
JOIN task ON partition1.task_id = task.id
WHERE partition1.rownum < 5
ORDER BY is_priority DESC NULLS LAST,
task_queue_id DESC
LIMIT
50
;
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
