Postgres order by using value from a joined table

Question

I have a table of conversations, and one of messages. I want to return a list of conversations ordered by their

select *
from "conversations"
         left outer join "messages" on "conversations"."sender_id" = "messages"."sender_id" and event = 'user'
order by (cast("messages"."parse_data" -> 'transcription' ->> 'confidence' as double precision)) nulls last fetch next 50 rows only

And here is the index created :

create index "messages_parse_data_transcription_index_bt"
    on messages using btree (sender_id, event, (cast("messages"."parse_data" -> 'transcription' ->> 'confidence' as double precision)) asc NULLS LAST );

analyse messages;

Unfortunately while it works on a large database (3GB, 7M lines on messages) it takes a very long time. Index creation is already very long (3m), and the query itself is also very very long (2M48)

Limit  (cost=523667.88..523673.63 rows=50 width=3052) (actual time=132421.177..142355.335 rows=50 loops=1)
  ->  Gather Merge  (cost=523667.88..560621.87 rows=321339 width=3052) (actual time=131738.458..141672.591 rows=50 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        ->  Sort  (cost=522667.87..523471.22 rows=321339 width=3052) (actual time=131626.455..131626.606 rows=48 loops=2)
              Sort Key: ((((messages.parse_data -> 'transcription'::text) ->> 'confidence'::text))::double precision)
              Sort Method: top-N heapsort  Memory: 125kB
              Worker 0:  Sort Method: top-N heapsort  Memory: 125kB
              ->  Parallel Hash Left Join  (cost=369055.84..511993.22 rows=321339 width=3052) (actual time=101533.553..131406.086 rows=279394 loops=2)
                    Hash Cond: ((conversations.sender_id)::text = (messages.sender_id)::text)
                    ->  Parallel Seq Scan on conversations  (cost=0.00..2634.32 rows=69832 width=33) (actual time=0.018..22.318 rows=59357 loops=2)
                    ->  Parallel Hash  (cost=281743.66..281743.66 rows=227615 width=3011) (actual time=90093.869..90093.870 rows=276748 loops=2)
                          Buckets: 2048  Batches: 512  Memory Usage: 2352kB
                          ->  Parallel Seq Scan on messages  (cost=0.00..281743.66 rows=227615 width=3011) (actual time=404.291..52209.756 rows=276748 loops=2)
                                Filter: ((event)::text = 'user'::text)
                                Rows Removed by Filter: 3163558
Planning Time: 20.895 ms
JIT:
  Functions: 27
"  Options: Inlining true, Optimization true, Expressions true, Deforming true"
"  Timing: Generation 18.348 ms, Inlining 174.073 ms, Optimization 889.102 ms, Emission 424.619 ms, Total 1506.142 ms"
Execution Time: 142365.711 ms

How can I speed up my query execution ?

Postgres order by using value from a joined table

0 Answers