Code Monkey home page Code Monkey logo

Comments (9)

malthe avatar malthe commented on July 24, 2024

Do you have an EXPLAIN ANALYZE that might help determine the needed index?

from pq.

pramsey avatar pramsey commented on July 24, 2024
thematrix=> explain analyze SELECT * FROM thematrix.queue WHERE q_name = 'matrix' AND dequeued_at IS NULL ORDER BY schedule_at nulls first, expected_at nulls last, id LIMIT 1;
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=221449.70..221449.70 rows=1 width=646) (actual time=560.917..560.918 rows=1 loops=1)
   ->  Sort  (cost=221449.70..222840.64 rows=556377 width=646) (actual time=560.917..560.917 rows=1 loops=1)
         Sort Key: schedule_at NULLS FIRST, expected_at, id
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Index Scan using priority_idx_no_queue on queue  (cost=0.42..218667.81 rows=556377 width=646) (actual time=0.182..388.776 rows=595240 loops=1)
               Index Cond: (q_name = 'matrix'::text)
 Planning time: 0.179 ms
 Execution time: 560.955 ms
(8 rows)

I did built a partial index with 'matrix' as the driving name instead of 'queue' but it didn't change the performance at all, it was exactly the same even using the other index.

from pq.

malthe avatar malthe commented on July 24, 2024

So it is supposed to use the index defined here:

create index if not exists priority_idx_%(name)s on %(name)s
      (schedule_at nulls first, expected_at nulls last, q_name)
    where dequeued_at is null
          and q_name = '%(name)s';

But perhaps you don't have that index defined.

@3manuek – I don't really understand why there couldn't just be a single index (or two indexes if you count the other index as well) rather than one per value for q_name, -re- bc835d7.

Seems to me like the right index here would be:

create index priority_idx on %(name)s
    (q_name, schedule_at nulls first, expected_at nulls first)
    where dequeued_at is null;

What's the reason for the priority_idx_no_ index actually?

from pq.

pramsey avatar pramsey commented on July 24, 2024

I did ensure I have a specific index for my queue name (though you have one for generic 'queue' and one for "everything else" and that seems like it should work fine too) and got the plan above. New indexes, re-indexing, analyzing, vacuuming... nothing moved the needle.

And yeah, it seems clear that the index should come into play, particularly for a SORT ... LIMIT 1 scenario, but it refused to, no matter what knobs I twiddle. I'm not sure if I should blame 9.6 or RDS, but something is awry.

from pq.

malthe avatar malthe commented on July 24, 2024

Can you paste in the table definition including the indexe(s) you have on there? Just so that there's nothing crazy hiding there.

from pq.

pramsey avatar pramsey commented on July 24, 2024
                                   Table "thematrix.queue"
   Column    |           Type           |                     Modifiers                      
-------------+--------------------------+----------------------------------------------------
 id          | bigint                   | not null default nextval('queue_id_seq'::regclass)
 enqueued_at | timestamp with time zone | not null default now()
 dequeued_at | timestamp with time zone | 
 expected_at | timestamp with time zone | 
 schedule_at | timestamp with time zone | 
 q_name      | text                     | not null
 data        | json                     | not null
Indexes:
    "queue_pkey" PRIMARY KEY, btree (id)
    "priority_idx_matrix" btree (schedule_at NULLS FIRST, expected_at NULLS FIRST, q_name) WHERE dequeued_at IS NULL AND q_name = 'matrix'::text
    "priority_idx_no_queue" btree (schedule_at NULLS FIRST, expected_at NULLS FIRST, q_name) WHERE dequeued_at IS NULL AND q_name <> 'queue'::text
    "priority_idx_queue" btree (schedule_at NULLS FIRST, expected_at NULLS FIRST, q_name) WHERE dequeued_at IS NULL AND q_name = 'queue'::text
Check constraints:
    "queue_q_name_check" CHECK (length(q_name) > 0)
Triggers:
    pq_insert AFTER INSERT ON queue FOR EACH ROW EXECUTE PROCEDURE pq_notify()

from pq.

malthe avatar malthe commented on July 24, 2024

Looks like your indexes are wrong: you have: expected_at NULLS FIRST rather than LAST.

from pq.

pramsey avatar pramsey commented on July 24, 2024

Did the pq code change at some point? Because these indexes were created by pq originally. That was four years ago now, so maybe there was a change in the interim.

from pq.

malthe avatar malthe commented on July 24, 2024

Yeah it changed here: d0602bd.

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.