I'm deleting data and I want to minimize time when rows or table is locked. Goal is to increase throughput of other queries.

Let's say inserting into temp table takes 10 seconds and delete statement takes 1 second:

CREATE TEMP TABLE IF NOT EXISTS tmp(
    Key1 BIGINT
);

INSERT INTO tmp (Key1)
SELECT "mytable"."Key1"
FROM "myschema"."MyTable"
WHERE ....
    

DELETE FROM "myschema"."MyTable" AS "mytable"
USING tmp
WHERE "mytable"."Key1" = tmp_closed_positions.Key1

If I replace TEMP table with a subquery in delete statement, can in increate time when rows or table is locked?

DELETE FROM "myschema"."MyTable" AS "mytable"
USING (SELECT "mytable"."Key1" AS Key1
       FROM "myschema"."MyTable"
       WHERE ....) AS tmp
WHERE "mytable"."Key1" = tmp.Key1;

I'm deleting data and I want to minimize time when rows or table is locked. Goal is to increase throughput of other queries.

Let's say inserting into temp table takes 10 seconds and delete statement takes 1 second:

CREATE TEMP TABLE IF NOT EXISTS tmp(
    Key1 BIGINT
);

INSERT INTO tmp (Key1)
SELECT "mytable"."Key1"
FROM "myschema"."MyTable"
WHERE ....
    

DELETE FROM "myschema"."MyTable" AS "mytable"
USING tmp
WHERE "mytable"."Key1" = tmp_closed_positions.Key1

If I replace TEMP table with a subquery in delete statement, can in increate time when rows or table is locked?

DELETE FROM "myschema"."MyTable" AS "mytable"
USING (SELECT "mytable"."Key1" AS Key1
       FROM "myschema"."MyTable"
       WHERE ....) AS tmp
WHERE "mytable"."Key1" = tmp.Key1;

• postgresql
• plpgsql

Share Improve this question Follow edited Mar 27 at 12:56 Liero asked Mar 25 at 13:45 LieroLiero 27.5k4141 gold badges179179 silver badges336336 bronze badges 9

• Bonus question: How do I verify it? I'm not very familiar with Postgres. – Liero Commented Mar 25 at 13:46
• Please share the DDL for table MyTable and its indexes and the results from explain, or even better, explain(analyze, verbose, buffers, settings) (DELETE will be executed!) for your current DELETE statement. I'm asking because it's unclear whether you even have a problem. – Frank Heikens Commented Mar 25 at 13:58
• 1) The temporay table you create tmp does not match the table you are getting the Key1 values from here WHERE "mytable"."Key1" = tmp_closed_positions.Key1. 2) What makes you think this is going to speed things up? The lock will still be taken when you do DELETE FROM "myschema"."MyTable" AS "mytable". You now have just added the time to create a temporary table. – Adrian Klaver Commented Mar 25 at 15:12
• @AdrianKlaver: I don't care about speed of this script. I care about how long is the table or rows locked. – Liero Commented Mar 26 at 7:41
• 1 Basically you are saying I don't care about speed, I just want it to be fast. – Adrian Klaver Commented Mar 26 at 17:09

 |  Show 4 more comments

1 Answer 1

Sorted by: Reset to default 1

Just replace the temporary table with a subquery:

DELETE FROM "myschema"."MyTable" AS "mytable"
USING (SELECT "mytable"."Key1" AS Key1
       FROM "myschema"."MyTable" AS "mytable"
       WHERE "mytable"."Type" = 3
         AND "mytable"."Timestamp" < NOW() - MAKE_INTERVAL(DAYS => 30)
       LIMIT 1000) AS tmp
WHERE "mytable"."Key1" = tmp.Key1;

If that subquery is expensive, it can take substantially longer to execute than using the temporary table. As a consequence, rows would be locked for a longer time.

The actual outcome is determined by the execution plan. To get a good answer, run EXPLAIN (ANALYZE) on the statement (attention: it will actually execute the DELETE!). The rows are locked by the "Lock Rows" node. In the "actual" parenteses, you'l find two "time" values: the time until the first row got locked and the time until the last row got locked. That should give you a conclusive answer.