Postgres query taking too much time on the first run

44 Views Asked by At

I have a a query which takes too much time on the very first run, and I need help reducing the time. It has too many large tables involved and has an index on all the columns involved in joins/where clause/order by clause. I am sharing the explain analyze result for some suggestions for reducing the time taken to execute this query.

Gather Merge  (cost=16474.38..16487.69 rows=114 width=246) (actual time=24169.085..24225.617 rows=331 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Sort  (cost=15474.36..15474.50 rows=57 width=246) (actual time=24015.878..24015.887 rows=110 loops=3)
        Sort Key: addres.share, ship.master_tracking
        Sort Method: quicksort  Memory: 71kB
        Worker 0:  Sort Method: quicksort  Memory: 71kB
        Worker 1:  Sort Method: quicksort  Memory: 70kB
        ->  Nested Loop  (cost=450.64..15472.70 rows=57 width=246) (actual time=1332.644..24014.884 rows=110 loops=3)
              ->  Nested Loop  (cost=450.08..15391.47 rows=115 width=186) (actual time=1066.578..22766.463 rows=556 loops=3)
                    ->  Nested Loop Semi Join  (cost=449.51..15070.09 rows=116 width=36) (actual time=1063.621..21932.130 rows=557 loops=3)
                          ->  Parallel Bitmap Heap Scan on ship  (cost=448.95..8780.92 rows=3171 width=52) (actual time=972.526..14376.908 rows=13719 loops=3)
                                Recheck Cond: ((admin_loc)::text = 'ABC'::text)
                                Heap Blocks: exact=14253
                                ->  Bitmap Index Scan on ship_idx07_admin_loc  (cost=0.00..447.05 rows=7611 width=0) (actual time=1107.608..1107.608 rows=54073 loops=1)
                                      Index Cond: ((admin_loc)::text = 'ABC'::text)
                          ->  Index Only Scan using package_idx05_ship_uid_edd on package  (cost=0.56..1.98 rows=1 width=16) (actual time=0.548..0.548 rows=0 loops=41157)
                                Index Cond: ((ship_uid = ship.uid) AND (edd_date= '2023-08-30'::date))
                                Heap Fetches: 611
                    ->  Index Scan using addres_pkey on address  (cost=0.56..2.77 rows=1 width=166) (actual time=1.492..1.492 rows=1 loops=1672)
                          Index Cond: (uid = ship.consig_add_uid)
              ->  Index Scan using add_aefs_pk on add_aefs  (cost=0.56..0.71 rows=1 width=60) (actual time=2.243..2.243 rows=0 loops=1667)
                    Index Cond: ((share)::text = (addres.share)::text)
                    Filter: (geo_rank > 100)
                    Rows Removed by Filter: 1
Planning Time: 1.007 ms
Execution Time: 24226.135 ms

I have tried changing work_mem from 512 MB to 900 MB, but there was not much progress. I also tried selecting specific columns within JOINS. The time taken is still high. I am somewhat new to this and appreciate any suggestions.

0

There are 0 best solutions below