[SOLVED] Optimize subquery with max postgresql

Issue

I’m using PostgreSQL 12.8 and I have two tables:

CREATE TABLE s (
    id     BIGINT PRIMARY KEY,
    type   VARCHAR,
    active BOOLEAN
);

With more than 230k rows, and

CREATE TABLE s_aud (
    id            BIGINT NOT NULL,
    type          VARCHAR,
    revision_id   INT4 NOT NULL,
    revision_type SMALLINT NOT NULL,
    CONSTRAINT s_aud_pk PRIMARY KEY (id, revision_id)
);

that contains more than 4M rows and is an apprend-only table where we store every add, update or delete operation done in s table, s_aud table does not contain any FK to s table. The problem is that I would like to execute the following query:

SELECT s.*, a.revision_id 
FROM s
JOIN (
  SELECT id, MAX(revision_id) AS revision_id 
  FROM s_aud 
  WHERE revision_type <> 2 AND type = 'X_TYPE'
  GROUP BY id
) a ON s.id = a.id 
WHERE s.type = 'X_TYPE' AND s.active = true;

Which is something like, get the latest revision_id of revision_type 2 of every id in the table.

If I execute the query, it takes more than 10 minutes to execute which is not acceptable, how can I improve it? I tried to add an index to:

CREATE INDEX s_aud_id_idx ON s_aud (id);
CREATE INDEX s_aud_revision_type_idx ON s_aud (revision_type);

But didn’t affected to the performance of the query, any ideas?

EDIT, EXPLAIN WITH (ANALYZE, VERBOSE, BUFFERS, format text)

QUERY PLAN                                                                                                                                                                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=138478.70..149103.60 rows=1 width=238) (actual time=432.466..327417.023 rows=744 loops=1)                                                                                                                             
  Output: s.id, s.type, s.active, (max(s_aud.revision_id))
  Inner Unique: true                                                                                                                                                                                                                     
  Join Filter: (s.id = s_aud.id)                                                                                                                                                                                                   
  Rows Removed by Join Filter: 276396                                                                                                                                                                                                    
  Buffers: shared hit=77360594 read=7600                                                                                                                                                                                                 
  I/O Timings: read=22.744                                                                                                                                                                                                               
  ->  Gather  (cost=1000.00..10021.67 rows=1 width=234) (actual time=0.296..1.279 rows=744 loops=1)                                                                                                                                      
        Output: s.id, s.type, s.active           
        Workers Planned: 2                                                                                                                                                                                                               
        Workers Launched: 2                                                                                                                                                                                                              
        Buffers: shared hit=218 read=7600                                                                                                                                                                                                
        I/O Timings: read=22.744                                                                                                                                                                                                         
        ->  Parallel Seq Scan on db_schema.s (cost=0.00..9021.57 rows=1 width=234) (actual time=0.077..23.769 rows=248 loops=3)                                                                                      
              Output: s.id, s.type, s.active     
              Filter: (s.active AND ((s.type)::text = 'X_TYPE'::text))                                                                                                                                                            
              Rows Removed by Filter: 76643                                                                                                                                                                                              
              Buffers: shared hit=218 read=7600                                                                                                                                                                                          
              I/O Timings: read=22.744                                                                                                                                                                                                   
              Worker 0: actual time=0.132..36.318 rows=463 loops=1                                                                                                                                                                       
                Buffers: shared hit=217 read=3811                                                                                                                                                                                        
                I/O Timings: read=11.326                                                                                                                                                                                                 
              Worker 1: actual time=0.016..34.903 rows=280 loops=1                                                                                                                                                                       
                Buffers: shared read=3785                                                                                                                                                                                                
                I/O Timings: read=11.401                                                                                                                                                                                                 
  ->  Finalize GroupAggregate  (cost=137478.70..138951.16 rows=5812 width=12) (actual time=439.679..440.021 rows=372 loops=744)                                                                                                          
        Output: s_aud.id, max(s_aud.revision_id)                                                                                                                                                                             
        Group Key: s_aud.id                                                                                                                                                                                                        
        Buffers: shared hit=26138272                                                                                                                                                                                                     
        ->  Gather Merge  (cost=137478.70..138834.92 rows=11624 width=12) (actual time=439.672..439.859 rows=1111 loops=744)                                                                                                             
              Output: s_aud.id, (PARTIAL max(s_aud.revision_id))                                                                                                                                                             
              Workers Planned: 2                                                                                                                                                                                                         
              Workers Launched: 2                                                                                                                                                                                                        
              Buffers: shared hit=26138272                                                                                                                                                                                               
              ->  Sort  (cost=136478.67..136493.20 rows=5812 width=12) (actual time=435.151..435.183 rows=581 loops=2232)                                                                                                                
                    Output: s_aud.id, (PARTIAL max(s_aud.revision_id))                                                                                                                                                       
                    Sort Key: s_aud.id                                                                                                                                                                                             
                    Sort Method: quicksort  Memory: 59kB                                                                                                                                                                                 
                    Worker 0:  Sort Method: quicksort  Memory: 59kB                                                                                                                                                                      
                    Worker 1:  Sort Method: quicksort  Memory: 59kB                                                                                                                                                                      
                    Buffers: shared hit=77360376                                                                                                                                                                                         
                    Worker 0: actual time=433.575..433.613 rows=689 loops=744                                                                                                                                                            
                      Buffers: shared hit=25663619                                                                                                                                                                                       
                    Worker 1: actual time=434.099..434.136 rows=682 loops=744                                                                                                                                                            
                      Buffers: shared hit=25627461                                                                                                                                                                                       
                    ->  Partial HashAggregate  (cost=136057.16..136115.28 rows=5812 width=12) (actual time=434.849..434.962 rows=741 loops=2232)                                                                                         
                          Output: s_aud.id, PARTIAL max(s_aud.revision_id)                                                                                                                                                   
                          Group Key: s_aud.id                                                                                                                                                                                      
                          Buffers: shared hit=77348472                                                                                                                                                                                   
                          Worker 0: actual time=433.259..433.372 rows=740 loops=744                                                                                                                                                      
                            Buffers: shared hit=25657667                                                                                                                                                                                 
                          Worker 1: actual time=433.781..433.894 rows=740 loops=744                                                                                                                                                      
                            Buffers: shared hit=25621509                                                                                                                                                                                 
                          ->  Parallel Seq Scan on db_schema.s_aud (cost=0.00..129536.26 rows=1304180 width=12) (actual time=0.017..285.222 rows=1039458 loops=2232)                                                   
                                Output: s_aud.id, s_aud.revision_id                                                                                                                                                          
                                Filter: ((s_aud.revision_type <> 2) AND ((s_aud.type)::text = 'X_TYPE'::text))                                                                                                        
                                Rows Removed by Filter: 324437                                                                                                                                                                           
                                Buffers: shared hit=77348472                                                                                                                                                                             
                                Worker 0: actual time=0.007..283.757 rows=1034585 loops=744                                                                                                                                              
                                  Buffers: shared hit=25657667                                                                                                                                                                           
                                Worker 1: actual time=0.007..284.260 rows=1033185 loops=744                                                                                                                                              
                                  Buffers: shared hit=25621509                                                                                                                                                                           
Planning Time: 0.187 ms    

                                                                                                                                                                                                          

Solution

Your indexes are not appropriate. In table s you look for s.type = 'X_TYPE' AND s.active = true, but there is no index on the columns it seems. In table s_aud you want revision_type <> 2 AND type = 'X_TYPE', and there is only an index on revision_type.

Have composite indexes or even partial indexes instead. Use the latter if you aways look at the same values, e.g. always at type = 'X_TYPE';

Composite indexes:

create index idx1 on s (type, active, id);
create index idx1 on s_aud (type, revision_type, id, revision_id);

Partial indexes:

create index idx3 on s (id) where type = 'X_TYPE' AND active = true;
create index idx4 on s_aud (id, revision_id) where revision_type <> 2 AND type = 'X_TYPE';

Aa a last option you could even partition your tables by type or active status.

Answered By – Thorsten Kettner

Answer Checked By – Senaida (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *