| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | Perry Smith <pedz(at)easesoftware(dot)com> |
| Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Help with large delete |
| Date: | 2022-04-16 15:33:57 |
| Message-ID: | 1939729.1650123237@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
regards, tom lane
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Adrian Klaver | 2022-04-16 15:54:00 | Re: Require details that can we see the password history to a User account in PostgreSQL Database. |
| Previous Message | Peter J. Holzer | 2022-04-16 14:47:20 | Re: Help with large delete |