115 votes
Accepted

Very slow DELETE in PostgreSQL, workaround?

I had a similar problem. As it turns out, those ON DELETE CASCADE triggers were slowing things down quite a bit, because those cascaded deletions were awfully slow. I solved the problem by creating ...
ailnlv's user avatar
  • 1,266
53 votes

Very slow DELETE in PostgreSQL, workaround?

The easiest method to solve the problem is to query detailed timing from the PostgreSQL: EXPLAIN. For this you need to find at minimum a single query that does complete but takes longer than expected. ...
Mikko Rantalainen's user avatar
42 votes
Accepted

DELETE rows which are not referenced in other table

Quoting the manual: There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the USING ...
Erwin Brandstetter's user avatar
20 votes
Accepted

How to delete duplicate records in MySQL, in a table without IDs?

There is no any field combination which identifies the record uniqually. I see at least 2 different solutions. First solution: move unique records to a copy of table and replace original table. ...
Akina's user avatar
  • 19.9k
19 votes

Delete rows older than x days without locking table

You need to delete rows in batches to avoid lock escalation. Lock escalation happens when SQL Server switches from row or page locks to locking the entire table. Lock escalation conserves memory ...
Stephen Morris - Mo64's user avatar
18 votes

Very slow DELETE in PostgreSQL, workaround?

Disabling triggers may be a threat to DB integrity and cannot be recommended; however if you are sure your operation is constraint-failure-proof, you can disable triggers, with the following: SET ...
Johann8's user avatar
  • 281
17 votes
Accepted

SQL estimates are way off on DELETE statement with Triggers on huge tables

Assuming all the related tables have correct indexing for the delete paths, you could try: DELETE [Trips] WHERE [ISAFileName]='ID_774199_20200311_133117.isa' OPTION (LOOP JOIN, FAST 1, USE HINT ('...
Paul White's user avatar
  • 84.3k
16 votes
Accepted

MySQL DELETE becomes exceptionaly slow with a large number of rows

With InnoDB, deleted rows must be held in case there is a crash or other reason to rollback the action. This is costly, and may get more costly with larger counts, as you are seeing. Plan A: Delete ...
Rick James's user avatar
  • 78.1k
15 votes
Accepted

MySQL - Delete row that has a foreign key constraint which reference to itself

Besides disabling foreign keys which is is dangerous and can lead to inconsistencies, there are two other options to consider: Modify the FOREIGN KEY constraints with the ON DELETE CASCADE option. I ...
ypercubeᵀᴹ's user avatar
14 votes

DELETE statement conflicted with the REFERENCE constraint

That is the whole point of foreign key constraints: they stop you deleting data that is referred to elsewhere in order to maintain referential integrity. There are two options: Delete the rows from ...
David Spillett's user avatar
14 votes

Large delete query seems to have frozen

First of all, check the SQL errorlog to see if it actually hit a max size for the log. If it did, then the query has no hope of completing, it is probably already in a rollback state. Even if it is, ...
BradC's user avatar
  • 9,964
14 votes

Is the system column "ctid" legitimate for identifying rows to delete?

The ROW SHARE lock taken by FOR UPDATE prevents concurrent write access that would change the physical location of the row. The manual: This prevents them from being locked, modified or deleted by ...
Erwin Brandstetter's user avatar
13 votes
Accepted

SQL Server Concurrent Delete/Insert Application

The only query in what you're showing above appears to be this one, repeated a few times: IF EXISTS ( select * from [dbo].[FinanceDetail] trd WITH (UPDLOCK, ...
Brent Ozar's user avatar
12 votes
Accepted

Truncate with where clause

No, Truncate can't be used with a WHERE clause. Truncate simply deallocates all the pages belonging to a table (or a partition) and its indexes. From BOL: -- Syntax for SQL Server and Azure SQL ...
Erik Darling's user avatar
  • 40.8k
12 votes

Delete query not deleting data

SQL Server never randomly chooses to delete or not delete a row. When you send a valid DELETE statement to SQL Server, it executes it. Guaranteed. Period. If there is an error in the statement, ...
Hannah Vernon's user avatar
  • 70.1k
12 votes
Accepted

How does SQL Server process DELETE WHERE EXISTS (SELECT 1 FROM TABLE)?

How does SQL Server know which rows to delete? To understand how it's processed, it's probably more helpful to look at the execution plan for the query. Setup scripts are at the end. First, let's ...
11 votes

MySQL - Delete row that has a foreign key constraint which reference to itself

SET FOREIGN_KEY_CHECKS=0; DELETE FROM forum WHERE Owner_Id = 1 ORDER BY nright; SET FOREIGN_KEY_CHECKS=1; just not forget in this case You must manually parse situations when parent_id show to 1, ...
a_vlad's user avatar
  • 3,655
11 votes

Large delete query seems to have frozen

DO NOT RESTART SQL SERVER. This will only prolong your agony since recovery will take place, which will rollback or redo any transactions not completed, including your delete. Killing the session ...
Hannah Vernon's user avatar
  • 70.1k
11 votes

DELETE on a heap table

Sure, put a clustered index on it. Tables with a clustered index will automatically deallocate space. Otherwise, you're looking at: ALTER TABLE (mytablename) REBUILD - which takes it offline Doing ...
Brent Ozar's user avatar
11 votes
Accepted

delete query takes forever

It seems likely the very large ntext data is highly fragmented, causing a large amount of random I/O (or other inefficiencies) when locating LOB fragments to delete. Maybe the elastic thingy needs ...
Paul White's user avatar
  • 84.3k
11 votes
Accepted

Deleting millions of records on SQL Server 14.0

For large deletes in batches, consider specifying a clustered index key range instead of using TOP so that a clustered index seek can be used in the plan. Below is an example. DECLARE @...
Dan Guzman's user avatar
  • 28.2k
10 votes
Accepted

Clustered index not used in a delete statement

I'd like to know why the optimizer does not use the clustered index, but is using the non-clustered one? This will be a decision of the cost based optimizer. It estimates that it is cheaper to ...
Martin Smith's user avatar
  • 84.7k
10 votes
Accepted

Releasing disk space by using DELETE or TRUNCATE?

From the docs on VACUUM There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM FULL can reclaim more disk space but runs much more slowly. Also, the standard form of VACUUM can ...
Evan Carroll's user avatar
  • 63.2k
10 votes
Accepted

Why is the 'DELETE' operation on a PostgreSQL database table unusually very slow?

As clearly stated in your execution plan, the actual DELETE takes just 44.801 ms. The rest of the time is spent checking the two triggers for constraints column_name_loading_fkey in the other two ...
Andrea B.'s user avatar
  • 1,558
9 votes
Accepted

Slow DELETEs of LOB data in SQL Server

...once they are no longer in cache, both selecting and deleting the same 12 thousand rows takes ~40 seconds. This seems to indicate that the storage subsystem is inadequate. If this is the cause, ...
Paul White's user avatar
  • 84.3k
9 votes
Accepted

PostgreSQL 9.5.2 DELETE taking forever

Add a index to dependents tables (for eficient deletes) If you have create table "table"( id integer primary key, added_at date ); create table other( other_id integer primary key, table_id ...
Emilio Platzer's user avatar
9 votes

Stored Procedure Asking for Input While Processing

Short answer is that a stored procedure cannot prompt to accept an input to utilize in the transaction after it has been executed. What you could do is use a stored procedure to obtain a count or the ...
Shaulinator's user avatar
  • 3,142
9 votes

SQL estimates are way off on DELETE statement with Triggers on huge tables

A table scan on cascade delete is a common symptom of not having proper indexes on your tables. Ensure that all of the FK tables have indexes supporting the foreign keys. IE a clustered or non-...
David Browne - Microsoft's user avatar
9 votes
Accepted

Delete statement not responding to Kill session command

I am at a loss as to what to do about this query to get it to rollback and also just understanding what is going on, can anyone suggest what I can look at? DELETE FROM [Some400MRowTable] is ...
David Browne - Microsoft's user avatar
8 votes

I need to run VACUUM FULL with no available disk space

Quick and dirty: Stop Postgres Move the main database directory to another disk where there's enough room for vacuuming In the original location of main, add a symlink to the new location Vacuum ...
Roger Dahl's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible