Re: ***SPAM*** Re: Help with large delete

> On Apr 16, 2022, at 10:33, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Perry Smith <pedz(at)easesoftware(dot)com> writes:
>> Currently I have one table that mimics a file system. Each entry has a parent_id and a base name where parent_id is an id in the table that must exist in the table or be null with cascade on delete.
>> I’ve started a delete of a root entry with about 300,000 descendants. The table currently has about 22M entries and I’m adding about 1600 entries per minute still. Eventually there will not be massive amounts of entries being added and the table will be mostly static.
>
> The most obvious question is do you have an index on the referencing
> column. PG doesn't require one to exist to create an FK; but if you
> don't, deletes of referenced rows had better be uninteresting to you
> performance-wise, because each one will cause a seqscan.

To try to reply to Peter’s question, I just now started:

psql -c "explain analyze delete from dateien where basename = '/mnt/pedz/Visual_Media'” find_dups

And it hasn’t replied yet. I hope you are not slapping your head muttering “this guy is an idiot!!” — in that this would not give you the plan you are asking for...

This is inside a BSD “jail” on a NAS. I’m wondering if the jail has a limited time and the other processes have consumed it all. In any case, if / when it replies, I will post the results.

For Tom’s question, here is the description of the table:

psql -c '\d dateien' find_dups
Table "public.dateien"
Column | Type | Collation | Nullable | Default
------------+--------------------------------+-----------+----------+-------------------------------------
id | bigint | | not null | nextval('dateien_id_seq'::regclass)
basename | character varying | | not null |
parent_id | bigint | | |
dev | bigint | | not null |
ftype | character varying | | not null |
uid | bigint | | not null |
gid | bigint | | not null |
ino | bigint | | not null |
mode | bigint | | not null |
mtime | timestamp without time zone | | not null |
nlink | bigint | | not null |
size | bigint | | not null |
sha1 | character varying | | |
created_at | timestamp(6) without time zone | | not null |
updated_at | timestamp(6) without time zone | | not null |
Indexes:
"dateien_pkey" PRIMARY KEY, btree (id)
"unique_dev_ino_for_dirs" UNIQUE, btree (dev, ino) WHERE ftype::text = 'directory'::text
"unique_parent_basename" UNIQUE, btree (COALESCE(parent_id, '-1'::integer::bigint), basename)
Foreign-key constraints:
"fk_rails_c01ebbd0bf" FOREIGN KEY (parent_id) REFERENCES dateien(id) ON DELETE CASCADE
Referenced by:
TABLE "dateien" CONSTRAINT "fk_rails_c01ebbd0bf" FOREIGN KEY (parent_id) REFERENCES dateien(id) ON DELETE CASCADE