Re: Deleting takes days, should I add some index?
Thank you for the explanation, David On Thu, Feb 25, 2021 at 9:49 PM David Rowley wrote: > > 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. > >
Re: Deleting takes days, should I add some index?
čt 25. 2. 2021 v 22:33 odesílatel Alexander Farber < alexander.far...@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 > wrote: > >> čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber < >>> alexander.far...@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 > Best regards > Alex >
Re: Deleting takes days, should I add some index?
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 wrote: > čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber < >> alexander.far...@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
Re: Deleting takes days, should I add some index?
čt 25. 2. 2021 v 22:02 odesílatel Pavel Stehule napsal: > Hi > > čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber < > alexander.far...@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 > Regards > > Pavel > >
Re: Deleting takes days, should I add some index?
Hi čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber < alexander.far...@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
Re: Deleting takes days, should I add some index?
On Fri, 26 Feb 2021 at 02:06, Alexander Farber 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 = ; 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 = ; 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,100) x; insert into t2 select x,x from generate_Series(1,100) x; insert into t3 select x,x from generate_Series(1,100) 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
Re: Deleting takes days, should I add some index?
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';
Re: Deleting takes days, should I add some index?
čt 25. 2. 2021 v 14:36 odesílatel Alexander Farber <
alexander.far...@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 | |
Re: Deleting takes days, should I add some index?
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 |
Re: Deleting takes days, should I add some index?
čt 25. 2. 2021 v 14:06 odesílatel Alexander Farber <
alexander.far...@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
> wrote:
>
>> Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera 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
> 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 | | |
>
Re: Deleting takes days, should I add some index?
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
wrote:
> Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera 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
Re: Deleting takes days, should I add some index?
Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera 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.
Re: 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.
Deleting takes days, should I add some index?
Hello,
I am using PostgreSQL 10.15 on CentOS 7 with 64 GB RAM, Intel i7 6700 and I
have the following 2 tables there:
words_ru=> \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 | | |
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"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
words_ru=> \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 | | |
puzzle | boolean | | not null | false
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_puzzle_idx" btree (puzzle)
"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_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
My word game is published since beginning of 2018 and I have that many
entries there:
words_ru=> select
