CODEWITHSUNDEEP
postgresql
Georgii Lvov
Georgii Lvov

Reputation: 2787

PostgreSQL Stored procedure performance

I use PostgreSQL and I have a table 'PERSON' in schema 'public' that looks like this:

    +----+-------------+-------+----------------------------+
    | id | internal_id | name  | created                    |
    +----+-------------+-------+----------------------------+
    | 1  | P0001-XX00  |  Bob  |  2021-05-24 22:10:01.93025 |
    +----+-------------+-------+----------------------------+
    | 2  | P0001-CX00  |  Tom  |  2021-06-27 22:10:01.93025 |
    +----+-------------+-------+----------------------------+
    | 3  | P0002-XX00  |  Anna |  2021-05-24 22:10:01.93025 |    
    +----+-------------+-------+----------------------------+

id -> bigint; internal_id -> character varying; name -> character varying; created  -> timestamp without timezone

I need to write procedure that delete those records that are older than fixed timestamp, for example: now(). But as soon as such an old record has been found, I need to check if there are other records in the table which are not old yet and with the same first 5 characters in internal_id as the found old record. If there are such records, then I should not delete the old record.

So I wrote the following procedure with plpgsql and it seems to work:

BEGIN
DELETE FROM public."PERSON" AS t1
   WHERE t1.created < now()
   AND NOT EXISTS
     (
      SELECT 
      FROM   public."PERSON" AS t2 
      WHERE  left(t2.internal_id, 5) = left(t1.internal_id, 5) 
      AND    t2.created >= now()
     );
COMMIT;
END;

Questions:

  1. Could it have been made more correct or prettier or cleaner? Perhaps, instead of using the left() function, it was necessary to use LIKE, or, in principle, to do it somehow differently?

  2. Do you think this procedure has normal performance or it can be improved?

Thank you in advance!

Upvotes: 0

Views: 211

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247960

That should work just fine.

For good performance, create an index:

CREATE INDEX ON public."PERSON" (left(internal_id, 5), created);

Upvotes: 1

Related Questions