EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

SELECT transaction seems to be cached on postgres?

Database type:

The server I am running distributes 'jobs' out to users, however, I'm having an issue where users get distributed the same job. I believe this is either a concurrency issue or a caching issue.

Here's the query I currently use (note the {...} is for python formatting):

UPDATE "job" 
SET pending=true, completor='{client.uuid}' 
WHERE "number" IN 
    (
     SELECT "number" FROM "job" 
     WHERE pending=false AND closed=false AND gpu=true 
     ORDER BY "number" ASC LIMIT 1
    );

Afterwards, I fetch the job where completor='{client.uuid}' and it fetches the job that was updated. I thought that using this system would make the entire process run in a single transaction, avoiding any concurrency issues.

However, after testing I found that clients can be given the same job even after 5 seconds apart from each other. Could this be a caching issue, or do I need to implement a locking mechanism?

Thanks in advance.

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

CREATE INDEX job_idx_number ON "job" ("number");
CREATE INDEX job_idx_pending_closed_gpu_number ON "job" ("pending","closed","gpu","number");

The optimized query:

SELECT
        "job".pending 
    FROM
        "job" 
    WHERE
        "job"."number" IN (
            SELECT
                "job"."number" 
            FROM
                "job" 
            WHERE
                "job".pending = false 
                AND "job".closed = false 
                AND "job".gpu = true 
            ORDER BY
                "job"."number" ASC LIMIT 1
        )

Question 2

EverSQL Database Performance Knowledge Base

SELECT transaction seems to be cached on postgres?

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles