Missing Index

[Dandush03]

Before creaeting index

EXPLAIN (ANALYZE, BUFFERS) SELECT DISTINCT "solid_queue_ready_executions"."queue_name" FROM "solid_queue_ready_executions" WHERE (queue_name LIKE 'banana%');
                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=4877.07..4877.08 rows=1 width=66) (actual time=4.932..4.933 rows=0 loops=1)
   Buffers: shared hit=4488
   ->  Sort  (cost=4877.07..4877.08 rows=1 width=66) (actual time=4.931..4.932 rows=0 loops=1)
         Sort Key: queue_name
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=4488
         ->  Seq Scan on solid_queue_ready_executions  (cost=0.00..4877.06 rows=1 width=66) (actual time=4.910..4.910 rows=0 loops=1)
               Filter: ((queue_name)::text ~~ 'banana%'::text)
               Rows Removed by Filter: 30143
               Buffers: shared hit=4485
 Planning:
   Buffers: shared hit=146
 Planning Time: 0.438 ms
 Execution Time: 4.991 ms
(14 rows)

Index Creation Command

CREATE INDEX CONCURRENTLY solid_queue_ready_executions_on_queue_name ON solid_queue_ready_executions (queue_name text_pattern_ops);

After Index Creation

EXPLAIN (ANALYZE, BUFFERS) SELECT DISTINCT "solid_queue_ready_executions"."queue_name" FROM "solid_queue_ready_executions" WHERE (queue_name LIKE 'banana%');
                                                                                        QUERY PLAN                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=8.32..8.33 rows=1 width=66) (actual time=0.031..0.032 rows=0 loops=1)
   Buffers: shared hit=2
   ->  Sort  (cost=8.32..8.32 rows=1 width=66) (actual time=0.031..0.031 rows=0 loops=1)
         Sort Key: queue_name
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=2
         ->  Index Only Scan using solid_queue_ready_executions_on_queue_name on solid_queue_ready_executions  (cost=0.29..8.31 rows=1 width=66) (actual time=0.013..0.013 rows=0 loops=1)
               Index Cond: ((queue_name ~>=~ 'banana'::text) AND (queue_name ~<~ 'bananb'::text))
               Filter: ((queue_name)::text ~~ 'banana%'::text)
               Heap Fetches: 0
               Buffers: shared hit=2
 Planning Time: 0.147 ms
 Execution Time: 0.057 ms
(13 rows)

[rosa]

Hey @Dandush03, that type of index is only available for PostgreSQL, so we can't add it. I've documented what you're seeing in the README. You can, of course, add this index to your Solid Queue DB. Perhaps this could be a good addition to the README, if you'd like to add it (although it'd help only in the case of using prefixes I think).

[Dandush03]

Thanks for your quick reply @rosa

OIC, so it’s a “pg only thing.” Makes sense. Even so, since we’re using DISTINCT, maybe adding a LIMIT could help reduce CPU usage a bit, especially with large tables. Wouldn’t solve everything, but it might be a lightweight improvement that works across all setups.

[rosa]

Even so, since we’re using DISTINCT, maybe adding a LIMIT could help

I think a LIMIT wouldn't help there because the number of distinct queue names is most likely quite small, and we need them all.

[rosa]

It's also worth noting that the solid_queue_ready_executions table should in general be very small. I wonder if Postgres's vacuuming is not correctly configured for you. I'm not an expert on this by any means, but there's some more info on this in #508 (comment), which is about a different query but it's possible the same tips apply.

I'm going to close this one in favour of that, but you can always add that index to your DB and see if that helps.