Скачать или смотреть How to Speed Up Slow DELETE Queries on Large SQL Tables

Discover effective strategies for optimizing delete queries on large SQL tables, specifically for PostgreSQL, ensuring better performance and quicker execution times.
---
This video is based on the question https://stackoverflow.com/q/64232594/ asked by the user 'DBA108642' ( https://stackoverflow.com/u/9908952/ ) and on the answer https://stackoverflow.com/a/64243896/ provided by the user 'wildplasser' ( https://stackoverflow.com/u/905902/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Delete query on a very large table running extremely slowly SQL

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Optimizing Slow Delete Queries on Large SQL Tables

If you've ever dealt with large databases, you know that a well-structured query can still run slowly, especially when modifying a vast number of records. One common challenge faced by many SQL developers is the slow execution of DELETE queries on large tables.

In this guide, we'll explore a specific scenario involving a DELETE query that affects a table with tens of millions of records, and we'll discuss strategies to speed up the process.

The Problem: Slow Delete Query

In our scenario, the DELETE query is intended to maintain a table called scans. Here's how the query looks:

[[See Video to Reveal this Text or Code Snippet]]

This query aims to delete all records older than three weeks, leaving only the records with minimum and maximum scandatetime for each unique imb. However, the execution time can be excessively long due to the size of the table and the complexity of the conditions.

Analyzing the Execution Plan

The EXPLAIN output reveals the query's complexity:

The index scan method is being used but still leads to a high execution cost.

It affects millions of rows, indicating potential inefficiencies in how records are filtered.

The Solution: Using EXISTS Instead of Subqueries

A More Efficient Query

To improve performance, consider rewriting the delete query by using EXISTS. This modification allows for a more efficient execution plan by reducing the need for multiple subqueries. Here's the revised query:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the New Query

Using EXISTS: The new query checks for the existence of records with the same imb that are both before and after the scandatetime of the record being deleted. This ensures only those records that are strictly in between are affected, which avoids the overhead of calculating minimum and maximum scandatetime values.

Performance Gains: By avoiding subselects for the min and max values, the SQL engine has to do considerably less work, especially by not retrieving and comparing every unique imb.

Conclusion

Speeding up slow DELETE queries in SQL, particularly in large PostgreSQL tables, can be a daunting task. However, by refining your query structure and utilizing efficient techniques like EXISTS, you can significantly improve performance.

Next time you find your delete operations lagging, consider revising your query structure using the suggestions outlined here—your database will thank you!