On Fri, 26 Feb 2021 at 02:06, Alexander Farber <alexander.farber@xxxxxxxxx> wrote: > However the deletion still takes forever and I have to ctrl-c it: > > # delete from words_games where created < now() - interval '12 month'; > > Do you please have any further suggestions? > > When I try to prepend "explain analyze" to the above query, then in the production database it also lasts forever. EXPLAIN with ANALYZE executes the query. So it'll probably to take just as long. Since your foreign keys perform a cascade delete on the tables referencing the tables you're deleting from, any records in those referencing tables will be deleted too. You must also look at those referencing tables and see what references those and index the column(s) which are referencing. Here's a simplified example that's easier to understand than your case. Setup: create table t1 (id int primary key); create table t2 (id int primary key, t1_id int not null references t1 on update cascade on delete cascade); create index on t2 (t1_id); create table t3 (id int primary key, t2_id int not null references t2 on update cascade on delete cascade); So I have 2 levels of reference. t2 -> t1 and t3 -> t2. If I remove a row from t1 then PostgreSQL must perform: DELETE FROM t2 WHERE t1_id = <id value of t1 row deleted>; Luckily I indexed t2(t1_id), so that should be fast. Since t3 references t2, the database must also perform: DELETE FROM t3 WHERE t2_id = <id value of t2 row deleted>; for the row that gets removed from t2. Unfortunately, I forgot to index t3(t2_id). Let me insert some data and see how the lack of index effects performance: insert into t1 select x from generate_Series(1,1000000) x; insert into t2 select x,x from generate_Series(1,1000000) x; insert into t3 select x,x from generate_Series(1,1000000) x; Delete 100 records. delete from t1 where id <= 100; DELETE 100 Time: 8048.975 ms (00:08.049) Pretty slow. create index on t3 (t2_id); CREATE INDEX (truncate t1 cascade and reinsert the data) delete from t1 where id <= 100; DELETE 100 Time: 5.151 ms Better. So, you need to follow each of the "Referenced by" from the table you're deleting from. In the \d output, just ignore the tables mentioned in "Foreign-key constraints:". Those are only checked on INSERT/UPDATE and must already contain a proper unique constraint and therefore index. David
