How to explain delete SQL query but not perform it? (I need figure the records to delete out)-CodePudding

I need to understand how many records will my sql query delete, but perform it later, after I understand the quantity is right. Does EXPLAIN DELETE FROM do this? ( I cannot experiment due to data value)

The RDBMS is PostgreSQL but I'm sure this is common for any RDBMS

CodePudding user response：

EXPLAIN ANd ANALYSE will show you how many rows are affected, but only when you add an actual filter to the query, like TheImpaler showed in the comment see https://dbfiddle.uk/?rdbms=postgres_14&fiddle=069785461713ea8f7bb9751a70396dbc

explain analyze delete from t where a % 2 = 0;

| QUERY PLAN                                                                                        |
| :------------------------------------------------------------------------------------------------ |
| Delete on t  (cost=0.00..48.25 rows=0 width=0) (actual time=0.028..0.029 rows=0 loops=1)          |
|   ->  Seq Scan on t  (cost=0.00..48.25 rows=13 width=6) (actual time=0.008..0.010 rows=3 loops=1) |
|         Filter: ((a % 2) = 0)                                                                     |
|         Rows Removed by Filter: 3                                                                 |
| Planning Time: 0.255 ms                                                                           |
| Execution Time: 0.121 ms                                                                          |

But basically every SELECT (COUNT(*) with your where clause should do the same

CREATE tABLE tab1(id int)

INSERT INTO tab1 VALUES(1)

INSERT INTO tab1 VALUES(2)

BEGIN;
DELETE FROM tab1;
ROLLBACK;

✓

2 rows affected

✓

db<>fiddle here