Deleting takes days, should I add some index?

On 2020-Nov-27, Alexander Farber wrote:

Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

Make sure you have indexes on the gid columns of these tables. Delete
needs to scan them in order to find the rows that are cascaded to.

So I ctrl-c (surprisingly not a single record was deleted; I was expecting
at least some to be gone)

Ctrl-C aborts the transaction, so even though the rows are marked
deleted, they are so by an aborted transaction. Therefore they're
alive.

Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera <alvherre@alvh.no-ip.org> a
écrit :

On 2020-Nov-27, Alexander Farber wrote:

Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY

(gid)

REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

Make sure you have indexes on the gid columns of these tables. Delete
needs to scan them in order to find the rows that are cascaded to.

An index on words_games(finished) and words_moves(played) would help too.

--
Guillaume.

Hello, revisiting an older mail on the too long deletion times (in
PostgreSQL 13.2)...

I have followed the advices here, thank you -

On Fri, Nov 27, 2020 at 4:15 PM Guillaume Lelarge <guillaume@lelarge.info>
wrote:

Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera <alvherre@alvh.no-ip.org> a
écrit :

On 2020-Nov-27, Alexander Farber wrote:

Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY

(gid)

REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY

(gid)

REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

Make sure you have indexes on the gid columns of these tables. Delete
needs to scan them in order to find the rows that are cascaded to.

An index on words_games(finished) and words_moves(played) would help too.

and have now the following indices in my database:

CREATE INDEX ON words_games(player1, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games(player2, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games(created),
CREATE INDEX ON words_chat(created),
CREATE INDEX ON words_moves(uid, action, played);
CREATE INDEX ON words_moves(gid, played);
CREATE INDEX ON words_moves(played);
CREATE INDEX ON words_moves(uid);
CREATE INDEX ON words_moves(gid);
CREATE INDEX ON words_social(uid, stamp);
CREATE INDEX ON words_geoip USING SPGIST (block);
CREATE INDEX ON words_scores(LENGTH(word), mid);
-- CREATE INDEX ON words_scores(uid, LENGTH(word) desc);
CREATE INDEX ON words_scores(gid);
CREATE INDEX ON words_scores(uid);
CREATE INDEX ON words_chat(gid);

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.

In an empty dev database the output does not help much -

# explain analyze delete from words_games where created < now() - interval
'12 month';
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Delete on words_games (cost=0.00..40.34 rows=1 width=6) (actual
time=0.132..0.132 rows=0 loops=1)
-> Seq Scan on words_games (cost=0.00..40.34 rows=1 width=6) (actual
time=0.131..0.131 rows=0 loops=1)
Filter: (created < (now() - '1 year'::interval))
Rows Removed by Filter: 137
Planning Time: 0.150 ms
Execution Time: 0.143 ms
(6 rows)

Below are the words_games and the "referenced by" tables -

# \d words_games
Table "public.words_games"
Column | Type | Collation | Nullable |
Default
----------+--------------------------+-----------+----------+------------------------------------------
gid | integer | | not null |
nextval('words_games_gid_seq'::regclass)
created | timestamp with time zone | | not null |
finished | timestamp with time zone | | |
player1 | integer | | not null |
player2 | integer | | |
played1 | timestamp with time zone | | |
played2 | timestamp with time zone | | |
state1 | text | | |
state2 | text | | |
reason | text | | |
hint1 | text | | |
hint2 | text | | |
score1 | integer | | not null |
score2 | integer | | not null |
chat1 | integer | | not null |
chat2 | integer | | not null |
hand1 | character(1)[] | | not null |
hand2 | character(1)[] | | not null |
pile | character(1)[] | | not null |
letters | character(1)[] | | not null |
values | integer[] | | not null |
bid | integer | | not null |
diff1 | integer | | |
diff2 | integer | | |
open1 | boolean | | not null | false
open2 | boolean | | not null | false
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"words_games_created_idx" btree (created)
"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
'infinity'::timestamp with time zone))
"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
'infinity'::timestamp with time zone))
Check constraints:
"words_games_chat1_check" CHECK (chat1 >= 0)
"words_games_chat2_check" CHECK (chat2 >= 0)
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

# \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
---------+--------------------------+-----------+----------+------------------------------------------
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb | | |
score | integer | | |
str | text | | |
hand | text | | |
letters | character(1)[] | | |
values | integer[] | | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_idx" btree (gid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_played_idx" btree (played)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE

# \d words_scores
Table "public.words_scores"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
mid | bigint | | not null |
gid | integer | | not null |
uid | integer | | not null |
word | text | | not null |
score | integer | | not null |
Indexes:
"words_scores_gid_idx" btree (gid)
"words_scores_length_mid_idx" btree (length(word) DESC, mid DESC)
"words_scores_uid_idx" btree (uid)
Check constraints:
"words_scores_score_check" CHECK (score >= 0)
"words_scores_word_check" CHECK (word ~ '^[А-Я]{2,}$'::text)
Foreign-key constraints:
"words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
ON DELETE CASCADE
"words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid)
ON DELETE CASCADE
"words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
ON DELETE CASCADE

čt 25. 2. 2021 v 14:06 odesílatel Alexander Farber <
alexander.farber@gmail.com> napsal:

Hello, revisiting an older mail on the too long deletion times (in
PostgreSQL 13.2)...

I have followed the advices here, thank you -

On Fri, Nov 27, 2020 at 4:15 PM Guillaume Lelarge <guillaume@lelarge.info>
wrote:

Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera <alvherre@alvh.no-ip.org> a
écrit :

On 2020-Nov-27, Alexander Farber wrote:

Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY

(gid)

REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY

(gid)

REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

Make sure you have indexes on the gid columns of these tables. Delete
needs to scan them in order to find the rows that are cascaded to.

An index on words_games(finished) and words_moves(played) would help too.

and have now the following indices in my database:

CREATE INDEX ON words_games(player1, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games(player2, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games(created),
CREATE INDEX ON words_chat(created),
CREATE INDEX ON words_moves(uid, action, played);
CREATE INDEX ON words_moves(gid, played);
CREATE INDEX ON words_moves(played);
CREATE INDEX ON words_moves(uid);
CREATE INDEX ON words_moves(gid);
CREATE INDEX ON words_social(uid, stamp);
CREATE INDEX ON words_geoip USING SPGIST (block);
CREATE INDEX ON words_scores(LENGTH(word), mid);
-- CREATE INDEX ON words_scores(uid, LENGTH(word) desc);
CREATE INDEX ON words_scores(gid);
CREATE INDEX ON words_scores(uid);
CREATE INDEX ON words_chat(gid);

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.

In an empty dev database the output does not help much -

# explain analyze delete from words_games where created < now() - interval
'12 month';
QUERY PLAN

------------------------------------------------------------------------------------------------------------
Delete on words_games (cost=0.00..40.34 rows=1 width=6) (actual
time=0.132..0.132 rows=0 loops=1)
-> Seq Scan on words_games (cost=0.00..40.34 rows=1 width=6) (actual
time=0.131..0.131 rows=0 loops=1)
Filter: (created < (now() - '1 year'::interval))
Rows Removed by Filter: 137
Planning Time: 0.150 ms
Execution Time: 0.143 ms
(6 rows)

Postgres newer use index on small tables

DELETE can be slow due ref integrity check or triggers. You should check so
all foreign keys have an index.

Regards

Pavel

Hi Pavel,

trying to follow your advice "You should check so all foreign keys have an
index" I look at the table where I want to delete older records:

# \d words_games
Table "public.words_games"
Column | Type | Collation | Nullable |
Default
----------+--------------------------+-----------+----------+------------------------------------------
gid | integer | | not null |
nextval('words_games_gid_seq'::regclass)
created | timestamp with time zone | | not null |
finished | timestamp with time zone | | |
player1 | integer | | not null |
player2 | integer | | |
played1 | timestamp with time zone | | |
played2 | timestamp with time zone | | |
state1 | text | | |
state2 | text | | |
reason | text | | |
hint1 | text | | |
hint2 | text | | |
score1 | integer | | not null |
score2 | integer | | not null |
chat1 | integer | | not null |
chat2 | integer | | not null |
hand1 | character(1)[] | | not null |
hand2 | character(1)[] | | not null |
pile | character(1)[] | | not null |
letters | character(1)[] | | not null |
values | integer[] | | not null |
bid | integer | | not null |
diff1 | integer | | |
diff2 | integer | | |
open1 | boolean | | not null | false
open2 | boolean | | not null | false
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"words_games_created_idx" btree (created)
"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
'infinity'::timestamp with time zone))
"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
'infinity'::timestamp with time zone))
Check constraints:
"words_games_chat1_check" CHECK (chat1 >= 0)
"words_games_chat2_check" CHECK (chat2 >= 0)
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

You are probably talking about the section:

Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE

The first table words_boards only has 4 records, so I ignore it.

The second table words_users already has an index on the uid, because that
column is the primary key:

# \d words_users
Table "public.words_users"
Column | Type | Collation | Nullable |
Default
-------------+--------------------------+-----------+----------+------------------------------------------
uid | integer | | not null |
nextval('words_users_uid_seq'::regclass)
created | timestamp with time zone | | not null |
visited | timestamp with time zone | | not null |
ip | inet | | not null |
fcm | text | | |
apns | text | | |
adm | text | | |
motto | text | | |
vip_until | timestamp with time zone | | |
grand_until | timestamp with time zone | | |
elo | integer | | not null |
medals | integer | | not null |
coins | integer | | not null |
avg_score | double precision | | |
avg_time | interval | | |
hms | text | | |
removed | boolean | | not null | false
muted | boolean | | not null | false
Indexes:
"words_users_pkey" PRIMARY KEY, btree (uid)
Check constraints:
"words_users_elo_check" CHECK (elo >= 0)
"words_users_medals_check" CHECK (medals >= 0)
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY
(player1) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY
(player2) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_puzzles" CONSTRAINT "words_puzzles_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_reviews" CONSTRAINT "words_reviews_author_fkey" FOREIGN
KEY (author) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_reviews" CONSTRAINT "words_reviews_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_stats" CONSTRAINT "words_stats_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE

Or do I misunderstand something?

If someone would be interested to take a look at the real database, I would
anonymize it and provide download

1 477 210 374 Feb 25 12:04 words_ru-Feb.sql.gz

However I understand that this is a lot to ask and am already thankful for
any input :-)

Thanks
Alex

čt 25. 2. 2021 v 14:36 odesílatel Alexander Farber <
alexander.farber@gmail.com> napsal:

Hi Pavel,

trying to follow your advice "You should check so all foreign keys have an
index" I look at the table where I want to delete older records:

# \d words_games
Table "public.words_games"
Column | Type | Collation | Nullable |
Default

----------+--------------------------+-----------+----------+------------------------------------------
gid | integer | | not null |
nextval('words_games_gid_seq'::regclass)
created | timestamp with time zone | | not null |
finished | timestamp with time zone | | |
player1 | integer | | not null |
player2 | integer | | |
played1 | timestamp with time zone | | |
played2 | timestamp with time zone | | |
state1 | text | | |
state2 | text | | |
reason | text | | |
hint1 | text | | |
hint2 | text | | |
score1 | integer | | not null |
score2 | integer | | not null |
chat1 | integer | | not null |
chat2 | integer | | not null |
hand1 | character(1)[] | | not null |
hand2 | character(1)[] | | not null |
pile | character(1)[] | | not null |
letters | character(1)[] | | not null |
values | integer[] | | not null |
bid | integer | | not null |
diff1 | integer | | |
diff2 | integer | | |
open1 | boolean | | not null | false
open2 | boolean | | not null | false
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"words_games_created_idx" btree (created)
"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
'infinity'::timestamp with time zone))
"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
'infinity'::timestamp with time zone))
Check constraints:
"words_games_chat1_check" CHECK (chat1 >= 0)
"words_games_chat2_check" CHECK (chat2 >= 0)
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

You are probably talking about the section:

Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE

The first table words_boards only has 4 records, so I ignore it.

The second table words_users already has an index on the uid, because that
column is the primary key:

# \d words_users
Table "public.words_users"
Column | Type | Collation | Nullable |
Default

-------------+--------------------------+-----------+----------+------------------------------------------
uid | integer | | not null |
nextval('words_users_uid_seq'::regclass)
created | timestamp with time zone | | not null |
visited | timestamp with time zone | | not null |
ip | inet | | not null |
fcm | text | | |
apns | text | | |
adm | text | | |
motto | text | | |
vip_until | timestamp with time zone | | |
grand_until | timestamp with time zone | | |
elo | integer | | not null |
medals | integer | | not null |
coins | integer | | not null |
avg_score | double precision | | |
avg_time | interval | | |
hms | text | | |
removed | boolean | | not null | false
muted | boolean | | not null | false
Indexes:
"words_users_pkey" PRIMARY KEY, btree (uid)
Check constraints:
"words_users_elo_check" CHECK (elo >= 0)
"words_users_medals_check" CHECK (medals >= 0)
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY
(player1) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY
(player2) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_puzzles" CONSTRAINT "words_puzzles_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_reviews" CONSTRAINT "words_reviews_author_fkey" FOREIGN
KEY (author) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_reviews" CONSTRAINT "words_reviews_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_stats" CONSTRAINT "words_stats_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE

Or do I misunderstand something?

If someone would be interested to take a look at the real database, I
would anonymize it and provide download

It is Linux or Windows?
is possible ssh access?

Pavel

Pavel, thank you for asking!

I have put the anonymized dump of my database at:

http://wordsbyfarber.com/words_dev.sql.gz (beware, it is a 1.3 GB download)

The question is why does the command take days (when I tried last time):

delete from words_games where created < now() - interval '12 month';

On Fri, 26 Feb 2021 at 02:06, Alexander Farber
<alexander.farber@gmail.com> 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

Hi

čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <
alexander.farber@gmail.com> napsal:

Pavel, thank you for asking!

I have put the anonymized dump of my database at:

http://wordsbyfarber.com/words_dev.sql.gz (beware, it is a 1.3 GB
download)

The question is why does the command take days (when I tried last time):

delete from words_games where created < now() - interval '12 month';

I tried to remove just only one row

postgres=# explain analyze delete from words_games where gid = 44877;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Delete on words_games (cost=0.42..8.44 rows=0 width=0) (actual
time=0.268..0.270 rows=0 loops=1) │
│ -> Index Scan using words_games_pkey on words_games (cost=0.42..8.44
rows=1 width=6) (actual time=0.205..0.209 rows=1 loops=1) │
│ Index Cond: (gid = 44877)
│
│ Planning Time: 0.328 ms
│
│ Trigger for constraint words_chat_gid_fkey on words_games: time=0.215
calls=1 │
│ Trigger for constraint words_moves_gid_fkey on words_games: time=0.240
calls=1 │
│ Trigger for constraint words_scores_gid_fkey on words_games: time=0.103
calls=1 │
│ Trigger for constraint words_puzzles_mid_fkey on words_moves: time=56.099
calls=4 │
│ Trigger for constraint words_scores_mid_fkey on words_moves:
time=22536.280 calls=4 │
│ Execution Time: 22593.293 ms
│
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(10 rows)

looks so you miss index

create index on words_scores(mid);

Regards

Pavel

čt 25. 2. 2021 v 22:02 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

Hi

čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <
alexander.farber@gmail.com> napsal:

Pavel, thank you for asking!

I have put the anonymized dump of my database at:

http://wordsbyfarber.com/words_dev.sql.gz (beware, it is a 1.3 GB
download)

The question is why does the command take days (when I tried last time):

delete from words_games where created < now() - interval '12 month';

I tried to remove just only one row

postgres=# explain analyze delete from words_games where gid = 44877;

┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│

╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Delete on words_games (cost=0.42..8.44 rows=0 width=0) (actual
time=0.268..0.270 rows=0 loops=1) │
│ -> Index Scan using words_games_pkey on words_games (cost=0.42..8.44
rows=1 width=6) (actual time=0.205..0.209 rows=1 loops=1) │
│ Index Cond: (gid = 44877)
│
│ Planning Time: 0.328 ms
│
│ Trigger for constraint words_chat_gid_fkey on words_games: time=0.215
calls=1 │
│ Trigger for constraint words_moves_gid_fkey on words_games: time=0.240
calls=1 │
│ Trigger for constraint words_scores_gid_fkey on words_games: time=0.103
calls=1 │
│ Trigger for constraint words_puzzles_mid_fkey on words_moves:
time=56.099 calls=4 │
│ Trigger for constraint words_scores_mid_fkey on words_moves:
time=22536.280 calls=4 │
│ Execution Time: 22593.293 ms
│

└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(10 rows)

looks so you miss index

create index on words_scores(mid);

postgres=# explain analyze delete from words_games where gid = 183154;
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
╞════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Delete on words_games (cost=0.42..8.44 rows=0 width=0) (actual
time=0.369..0.371 rows=0 loops=1) │
│ -> Index Scan using words_games_pkey on words_games (cost=0.42..8.44
rows=1 width=6) (actual time=0.283..0.288 rows=1 loops=1) │
│ Index Cond: (gid = 183154)
│
│ Planning Time: 0.230 ms
│
│ Trigger for constraint words_chat_gid_fkey on words_games: time=0.131
calls=1 │
│ Trigger for constraint words_moves_gid_fkey on words_games: time=1.329
calls=1 │
│ Trigger for constraint words_scores_gid_fkey on words_games: time=1.704
calls=1 │
│ Trigger for constraint words_puzzles_mid_fkey on words_moves: time=4.068
calls=51 │
│ Trigger for constraint words_scores_mid_fkey on words_moves: time=5.304
calls=51 │
│ Execution Time: 13.037 ms
│
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(10 rows)

Regards

Pavel

Thank you, Pavel!

I didn't even think about trying to "explain analyze" deletion of just 1
record -

On Thu, Feb 25, 2021 at 10:04 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <

alexander.farber@gmail.com> napsal:

The question is why does the command take days (when I tried last time):
delete from words_games where created < now() - interval '12 month';

postgres=# explain analyze delete from words_games where gid = 44877;

create index on words_scores(mid);

I have also added:

create index on words_puzzles(mid);

and then the result if finally good enough for my nightly cronjob:

explain analyze delete from words_games where created < now() - interval
'12 month';
QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------
Delete on words_games (cost=0.00..49802.33 rows=104022 width=6) (actual
time=2121.475..2121.476 rows=0 loops=1)
-> Seq Scan on words_games (cost=0.00..49802.33 rows=104022 width=6)
(actual time=0.006..85.908 rows=103166 loops=1)
Filter: (created < (now() - '1 year'::interval))
Rows Removed by Filter: 126452
Planning Time: 0.035 ms
Trigger for constraint words_chat_gid_fkey on words_games: time=598.444
calls=103166
Trigger for constraint words_moves_gid_fkey on words_games: time=83745.244
calls=103166
Trigger for constraint words_scores_gid_fkey on words_games:
time=30638.420 calls=103166
Trigger for constraint words_puzzles_mid_fkey on words_moves:
time=15426.679 calls=3544242
Trigger for constraint words_scores_mid_fkey on words_moves:
time=18546.115 calls=3544242
Execution Time: 151427.183 ms
(11 rows)

There is one detail I don't understand in the output of "explain analyze" -
why do the lines

"Trigger for constraint words_scores_mid_fkey on words_moves: time=1885.372
calls=4"

completely disappear after adding the index? Are those the "ON DELETE
CASCADE" triggers?

Aren't they called after the index has been added?

Best regards
Alex

čt 25. 2. 2021 v 22:33 odesílatel Alexander Farber <
alexander.farber@gmail.com> napsal:

Thank you, Pavel!

I didn't even think about trying to "explain analyze" deletion of just 1
record -

On Thu, Feb 25, 2021 at 10:04 PM Pavel Stehule <pavel.stehule@gmail.com>
wrote:

čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <

alexander.farber@gmail.com> napsal:

The question is why does the command take days (when I tried last time):
delete from words_games where created < now() - interval '12 month';

postgres=# explain analyze delete from words_games where gid = 44877;

create index on words_scores(mid);

I have also added:

create index on words_puzzles(mid);

and then the result if finally good enough for my nightly cronjob:

explain analyze delete from words_games where created < now() - interval
'12 month';
QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------
Delete on words_games (cost=0.00..49802.33 rows=104022 width=6) (actual
time=2121.475..2121.476 rows=0 loops=1)
-> Seq Scan on words_games (cost=0.00..49802.33 rows=104022 width=6)
(actual time=0.006..85.908 rows=103166 loops=1)
Filter: (created < (now() - '1 year'::interval))
Rows Removed by Filter: 126452
Planning Time: 0.035 ms
Trigger for constraint words_chat_gid_fkey on words_games: time=598.444
calls=103166
Trigger for constraint words_moves_gid_fkey on words_games:
time=83745.244 calls=103166
Trigger for constraint words_scores_gid_fkey on words_games:
time=30638.420 calls=103166
Trigger for constraint words_puzzles_mid_fkey on words_moves:
time=15426.679 calls=3544242
Trigger for constraint words_scores_mid_fkey on words_moves:
time=18546.115 calls=3544242
Execution Time: 151427.183 ms
(11 rows)

There is one detail I don't understand in the output of "explain analyze"
- why do the lines

"Trigger for constraint words_scores_mid_fkey on words_moves:
time=1885.372 calls=4"

completely disappear after adding the index? Are those the "ON DELETE
CASCADE" triggers?

these triggers are RI triggers

Aren't they called after the index has been added?

it should be called every time

Pavel

Thank you for the explanation, David

On Thu, Feb 25, 2021 at 9:49 PM David Rowley <dgrowleyml@gmail.com> wrote: