How can I improve the performance of this query?

Question

I've tried to add an index on records.status, but the cardinality is so low (<5 unique values) a seq scan was still used.

The query:

select distinct "events".* from "events" 
inner join (select "records".* from "records" where "status" = 'Mined') as "record" on "record"."guid" = CAST("events"."return_values"#>>'{guid}' AS text) 
where "events"."status" = 'Waiting' and "event" in ('RecordUpdated', 'RecordDiscovery', 'RecordRetrieval') 
order by "block_number" asc, "transaction_index" asc, "transaction_hash" asc, "log_index" asc 
limit 100;

The execution plan looks like:

 Limit  (cost=122673.00..122677.25 rows=100 width=1639) (actual time=575.631..575.632 rows=0 loops=1)
   ->  Unique  (cost=122673.00..124045.11 rows=32285 width=1639) (actual time=548.071..548.071 rows=0 loops=1)
         ->  Sort  (cost=122673.00..122753.71 rows=32285 width=1639) (actual time=548.068..548.069 rows=0 loops=1)
               Sort Key: events.block_number, events.transaction_index, events.transaction_hash, events.log_index, events.event_id, events.block_hash, events.address, events.return_values, events.event, events.signature, events.raw, events.processing_error, events.confirmations, events.created_at, events.updated_at, events.id
               Sort Method: quicksort  Memory: 25kB
               ->  Gather  (cost=47192.22..97302.09 rows=32285 width=1639) (actual time=548.048..571.664 rows=0 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     ->  Parallel Hash Join  (cost=46192.22..93073.59 rows=13452 width=1639) (actual time=526.239..526.241 rows=0 loops=3)
                           Hash Cond: ((events.return_values #>> '{guid}'::text[]) = (records.guid)::text)
                           ->  Parallel Index Scan using events_status_index on events  (cost=0.43..46843.84 rows=13804 width=1639) (actual time=0.106..36.976 rows=16870 loops=3)
                                 Index Cond: (status = 'Waiting'::event_status)
                                 Filter: ((event)::text = ANY ('{RecordUpdated,RecordDiscovery,RecordRetrieval}'::text[]))
                                 Rows Removed by Filter: 1
                           ->  Parallel Hash  (cost=42957.65..42957.65 rows=258731 width=44) (actual time=462.763..462.764 rows=205834 loops=3)
                                 Buckets: 1048576  Batches: 1  Memory Usage: 56672kB
                                 ->  Parallel Seq Scan on records  (cost=0.00..42957.65 rows=258731 width=44) (actual time=16.322..244.807 rows=205834 loops=3)
                                       Filter: (status = 'Mined'::record_status)
                                       Rows Removed by Filter: 6625
 Planning Time: 0.432 ms
 JIT:
   Functions: 50
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 8.788 ms, Inlining 0.000 ms, Optimization 3.711 ms, Emission 71.684 ms, Total 84.183 ms
 Execution Time: 602.684 ms
(25 rows)

events table:

                                          Table "public.events"
      Column       |           Type           | Collation | Nullable |              Default
-------------------+--------------------------+-----------+----------+------------------------------------
 event_id          | character varying(255)   |           | not null |
 log_index         | integer                  |           | not null |
 transaction_index | integer                  |           | not null |
 transaction_hash  | character varying(255)   |           | not null |
 block_hash        | character varying(255)   |           | not null |
 block_number      | integer                  |           | not null |
 address           | character varying(255)   |           | not null |
 return_values     | jsonb                    |           | not null |
 event             | character varying(255)   |           | not null |
 signature         | character varying(255)   |           | not null |
 raw               | jsonb                    |           | not null |
 status            | event_status             |           | not null |
 processing_error  | character varying(255)   |           |          |
 confirmations     | integer                  |           | not null |
 created_at        | timestamp with time zone |           |          |
 updated_at        | timestamp with time zone |           |          |
 id                | integer                  |           | not null | nextval('events_id_seq'::regclass)
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
    "events_block_log" btree (block_number, log_index)
    "events_status_index" btree (status)

records table:

                            Table "public.records"
      Column      |           Type           | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
 guid             | character varying(255)   |           | not null |
 data_hash        | character varying(255)   |           | not null |
 transaction_hash | character varying(255)   |           | not null |
 url              | character varying(255)   |           | not null |
 status           | record_status            |           | not null |
 created_at       | timestamp with time zone |           |          |
 updated_at       | timestamp with time zone |           |          |
 client_id        | character varying(64)    |           | not null |
 event_id         | character varying(255)   |           |          |
 user_id          | character varying(255)   |           |          |
Indexes:
    "records_pkey" PRIMARY KEY, btree (guid)
    "records_client_id_index" btree (client_id)
    "records_event_id_index" btree (event_id)
    "records_user_id_index" btree (user_id)
Foreign-key constraints:
    "records_client_id_foreign" FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
Referenced by:
    TABLE "discoveries" CONSTRAINT "discoveries_record_guid_foreign" FOREIGN KEY (record_guid) REFERENCES records(guid) ON DELETE CASCADE
    TABLE "record_dependencies" CONSTRAINT "record_dependencies_record_foreign" FOREIGN KEY (record) REFERENCES records(guid) ON DELETE CASCADE
    TABLE "retrievals" CONSTRAINT "retrievals_record_guid_foreign" FOREIGN KEY (record_guid) REFERENCES records(guid) ON DELETE CASCADE

Any tips would be greatly appreciated

Edit based on comments:

Here are the results of turning off seq_scans and io timing on

 Limit  (cost=149630.09..149634.34 rows=100 width=1639) (actual time=547.957..547.957 rows=0 loops=1)
   Buffers: shared hit=225599
   ->  Unique  (cost=149630.09..151002.20 rows=32285 width=1639) (actual time=526.203..526.204 rows=0 loops=1)
         Buffers: shared hit=225599
         ->  Sort  (cost=149630.09..149710.80 rows=32285 width=1639) (actual time=526.192..526.193 rows=0 loops=1)
               Sort Key: events.block_number, events.transaction_index, events.transaction_hash, events.log_index, events.event_id, events.block_hash, events.address, events.return_values, events.event, events.signature, events.raw, events.processing_error, events.confirmations, events.created_at, events.updated_at, events.id
               Sort Method: quicksort  Memory: 25kB
               Buffers: shared hit=225599
               ->  Gather  (cost=1000.86..124259.17 rows=32285 width=1639) (actual time=526.164..545.039 rows=0 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     Buffers: shared hit=225599
                     ->  Nested Loop  (cost=0.85..120030.67 rows=13452 width=1639) (actual time=505.207..505.208 rows=0 loops=3)
                           Buffers: shared hit=225599
                           ->  Parallel Index Scan using events_status_index on events  (cost=0.43..46843.84 rows=13804 width=1639) (actual time=12.540..58.632 rows=17030 loops=3)
                                 Index Cond: (status = 'Waiting'::event_status)
                                 Filter: ((event)::text = ANY ('{RecordUpdated,RecordDiscovery,RecordRetrieval}'::text[]))
                                 Rows Removed by Filter: 1
                                 Buffers: shared hit=21233
                           ->  Index Scan using records_pkey on records  (cost=0.43..5.30 rows=1 width=44) (actual time=0.024..0.024 rows=0 loops=51091)
                                 Index Cond: ((guid)::text = (events.return_values #>> '{guid}'::text[]))
                                 Filter: (status = 'Mined'::record_status)
                                 Rows Removed by Filter: 1
                                 Buffers: shared hit=204366
 Planning Time: 0.410 ms
 JIT:
   Functions: 41
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 7.868 ms, Inlining 0.000 ms, Optimization 2.929 ms, Emission 55.472 ms, Total 66.269 ms
 Execution Time: 570.247 ms

Answer 1

Using WHERE EXISTS() with a correlated suqeuery instead of a JOIN is one option since you are not returning anything from the joined table.

Something like (untested):

select "events".* 
from "events" 
WHERE exists (select 1 from "records" 
              where "record"."status" = 'Mined')  
              AND "record"."guid" = CAST("events"."return_values"#>>'{guid}' AS text)) 
      AND "events"."status" = 'Waiting' 
      AND "events"."event" in ('RecordUpdated', 'RecordDiscovery', 'RecordRetrieval') 
order by "block_number" asc, "transaction_index" asc, "transaction_hash" asc, "log_index" asc 
limit 100;

You can also add an index on the JSONB field for the key you are linking on.

Answer 2

Do you really need that "distinct" on the top level select?

This is always a Red Flag to me, too-often added as a "quick and easy" way to "get rid" of "duplicate" rows that appear in the result set "for some reason".

Even worse ...

select distinct events.* ... 

... has to perform that de-duplication across all the columns in the table. I'm guessing there are "a lot", which is why you chose to use "*" (which, BTW, you never should in Production code) and that could make for a lot of work from your database.

Your query is only ordered by four fields, but just check out the Sort Key in the Execution Plan (reformatted for emphasis):

Sort  (cost=149630.09..149710.80 rows=32285 width=1639) (actual time=526.192..526.193 rows=0 loops=1)
   Sort Key: events.block_number
           , events.transaction_index
           , events.transaction_hash
           , events.log_index
           , events.event_id
           , events.block_hash
           , events.address
           , events.return_values
           , events.event
           , events.signature
           , events.raw
           , events.processing_error
           , events.confirmations
           , events.created_at
           , events.updated_at
           , events.id

DISTINCT at its sneakiest worst!

Answer 3

The only thing i see is the GUID are text which is the slowest and widest storage method for this type.

See if the GUID can be converted to Postgresql UUID type which turns them into Integer type. Its faster and uses almost half as much space for the index and space for those columns.