Re: Performance degrade on insert on conflict do nothing

[Durgamahesh Manne <maheshpostgres9@xxxxxxxxx>]

Hi Greg

Great response from you this worked 

Regards

Durga Mahesh 

On Wed, Sep 11, 2024 at 7:12 PM Greg Sabino Mullane <htamfids@xxxxxxxxx> wrote:

On Wed, Sep 11, 2024 at 1:02 AM Durgamahesh Manne <maheshpostgres9@xxxxxxxxx> wrote:

Hi 

 createdat      | timestamp with time zone |           | not null | now()    | plain    |             |              |

 modified       | timestamp with time zone |           | not null | now()    | plain    |             |              |
Triggers:
    i_dictionary_createdat BEFORE INSERT ON dictionary FOR EACH ROW EXECUTE FUNCTION update_createdat_col()
    i_dictionary_modified BEFORE INSERT OR UPDATE ON dictionary FOR EACH ROW EXECUTE FUNCTION update_modified_col()

 

How do we improve this query performance without taking more cpu?

1. Lose the first trigger. I don't know exactly what those functions do, but if they are only for updating those columns, just remove the first one and let postgres handle it via NOT NULL DEFAULT.

2. Change the second trigger to just ON UPDATE

3. Remove that second trigger as well, and have the app populate that column (assuming that is all it does), e.g. UPDATE dictionary SET lang = 'foo', modified = DEFAULT, modified_by = 'foo' where tid = 12345;

4. Remove any indexes you do not absolutely need

Cheers,

Greg