Code Monkey home page Code Monkey logo

Comments (1)

mhjohnson avatar mhjohnson commented on July 4, 2024 2

Important clarification to add to the discussion:

The issue isn't just the length of time it takes to run the single query. It averages approximately 4ms for us - not murderous. The problem is compounded by the number of calls made and its aggregate cost.

Screenshot:
alt text

The problem occurs when we use pq to iterates over get() in a loop for a non-existent q_name in the table. This can happen in practice if old records for the q_name are purged from the table. A single worker thread makes ~1000/calls per minute in this scenario in our experience. This behavior is not present in situations where there is nothing to dequeue for q_name that still contains previously dequeued rows.

We could address this on our end by invoking a sleep when queue.get() returns None. But, we would like to ensure it can't be addressed in an upstream fashion beforehand.

Example Explains:

                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Update on tasks  (cost=115.98..124.00 rows=1 width=1122)
   CTE candidates
     ->  Limit  (cost=0.42..115.24 rows=2 width=740)
           ->  Index Scan using priority_idx2 on tasks tasks_1  (cost=0.42..4478.40 rows=78 width=740)
                 Index Cond: (q_name = 'faxer'::text)
   CTE selected
     ->  Limit  (cost=0.07..0.07 rows=1 width=24)
           ->  Sort  (cost=0.07..0.07 rows=1 width=24)
                 Sort Key: candidates.schedule_at NULLS FIRST, candidates.expected_at NULLS FIRST
                 ->  CTE Scan on candidates  (cost=0.00..0.06 rows=1 width=24)
                       Filter: (pg_try_advisory_xact_lock(id) AND ((schedule_at <= now()) OR (schedule_at IS NULL)))
   CTE next_timeout
     ->  Aggregate  (cost=0.06..0.07 rows=1 width=8)
           ->  CTE Scan on candidates candidates_1  (cost=0.00..0.05 rows=1 width=8)
                 Filter: (schedule_at >= now())
   InitPlan 4 (returns $3)
     ->  CTE Scan on next_timeout  (cost=0.00..0.02 rows=1 width=8)
   InitPlan 5 (returns $4)
     ->  CTE Scan on selected  (cost=0.00..0.02 rows=1 width=8)
   ->  Index Scan using tasks_pkey on tasks  (cost=0.56..8.58 rows=1 width=1122)
         Index Cond: (id = $4)
         Filter: (dequeued_at IS NULL)
(22 rows)

from pq.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.