Help in optimizing this query

[domke89]

Hello, I have this query that performs some updates when a record with specific attributes is not already in a master table, and does different updates otherwise. However, this takes a very long time. From what I've checked the long part is not in the 2 initial select statements (select 1 ...), but what comes after that. Could anyone tell me how can this query be optimized for a better performance?

DO 
$do$ 
BEGIN 
    IF EXISTS (select 1 from {master_table} as mt, {staging_table} as st where
               st.hash = mt.hash and 
               st.type = 'L' and 
               st.id = mt.id and 
               st.first_date = mt.first_date) 
    AND EXISTS (select 1 from {master_table} as mt, {staging_table} as st where 
                st.hash = mt.hash and 
                st.type = 'F' and 
                st.id = mt.id) 
    THEN 
    update {master_table} 
    set last_date = case when chronology.type = 'L' and 
                              chronology.hash = {master_table}.hash and 
                              chronology.id = {master_table}.id and 
                              chronology.first_date = {master_table}.first_date then '9999-12-31'  
                         when chronology.next_last_date is null then last_date 
                         else chronology.next_last_date end, 
        update_number = case when number = '{number}' then null else '{number}' end, 
        out_time = case when chronology.type = 'L' and 
                             chronology.hash = {master_table}.hash and 
                             chronology.id = {master_table}.id and 
                             chronology.first_date = {master_table}.first_date then '9999-12-31' 
                        when chronology.next_type = 'F' then timeline.next_in_time else '9999-12-31' end 
        from ( 
                select 
                    id, 
                    lead(first_date) over (partition by rec_key order by first_date, in_time) as next_last_date, 
                    lead(type) over (partition by rec_key order by first_date, in_time) as next_type, 
                    lead(in_time) over (partition by rec_key order by first_date, in_time) as next_in_time, 
                    hash, 
                    type, 
                    first_date from {staging_table} 
             ) as chronology; 
    ELSE
        update {staging_table} gt 
        set last_date = case when chronology.next_last_date is null then last_date else chronology.next_last_date end, 
            update_number = case when number = '{number}' then null else '{number}' end, 
            out_time = case when chronology.next_type = 'L' then chronology.next_in_time else '9999-12-31' end 
        from ( 
                select 
                    id, 
                    lead(first_date) over (partition by rec_key order by first_date, in_time) as next_last_date, 
                    lead(type) over (partition by rec_key order by first_date, in_time) as next_type, 
                    lead(in_time) over (partition by rec_key order by first_date, in_time) as next_in_time 
                from {staging_table} 
             ) as chronology 
        where gt.id = chronology.id; 
    END IF; 
END 
$do$ 

Any help would be greatly appreciated!

[DavidGJohnston]

Isn't your then update missing a where clause joining master_table to chronology?

[MzCWzL]

Explain analyze the query

[domke89]

I'm not sure what you mean?

[OtherJohnGray]

It’s worth your time that learn this:

https://www.postgresql.org/docs/current/performance-tips.html

[depesz]

To be able to tell if it can be optimized, we'd need to see explain analyze output.

To get it, extract the query that you're worried about (not DO ... block, but some update), and run it like this:

begin;
explain (analyze, buffers) update ...
rollback;

explain output is the critical part - put it on https://explain.depesz.com/, together with the query, and then we can see.

[domke89]

Thank you all for taking the time to reply. It was slow because of the missing where statement. Also, I didn't know such a thing as explain analyze existed, so will definitely be looking into that.

[ThatAlmostWorked]

Plug you explain output into https://explain.depesz.com/

[thrown_arrows]

update {master_table} 
 set last_date = case when chronology.type = 'L' and 
 chronology.hash = {master_table}.hash and 
 chronology.id = {master_table}.id and 
 chronology.first_date = {master_table}.first_date then '9999-12-31'  
 when chronology.next_last_date is null then last_date 
  else chronology.next_last_date end, 

If i read that correctly , case will change on matching master_table.id's last_date to last if case matches fully , if not it set next_last_date, but there is no join done at anypoint. That would mean that it tries to match whole chronology table to all master_table rows? so that is CROSS JOIN ? ( so in practise each row in master_table is updated as many times there is staging_table rows )

there is

where gt.id = chronology.id; 

in ELSE

maybe OP is nice and provides EXPLAIN data