Add an index to parent_id. What is likely happening is each time a row is
deleted, it has to scan the entire table to make sure it is not referenced
by any parent_id records.
On Thu, Jan 18, 2024 at 12:04 PM Jim Vanns wrote:
> After dropping the constraint entirely the DELETE completes in 4
> m
After dropping the constraint entirely the DELETE completes in 4
minutes (the same time as the dry-run using SELECT against the
function instead of a DELETE). A marked improvement on 3 hours
followed by a pg_cancel_backend()!
Jim
On Thu, 18 Jan 2024 at 16:37, Jim Vanns wrote:
>
> Hi Tom/Adrian.
Jim Vanns writes:
> I should have already stated I did begin with EXPLAIN but given they
> don't easily work with (the internals) stored/procedures, it wasn't
> useful in this case.
auto_explain with nested statements enabled might help?
regards, tom lane
> On Jan 18, 2024, at 9:46 AM, Adrian Klaver wrote:
>
> On 1/18/24 08:37, Jim Vanns wrote:
>> Hi Tom/Adrian.
>> I should have already stated I did begin with EXPLAIN but given they
>> don't easily work with (the internals) stored/procedures, it wasn't
>> useful in this case. Also, I keep havi
On 1/18/24 08:37, Jim Vanns wrote:
Hi Tom/Adrian.
I should have already stated I did begin with EXPLAIN but given they
don't easily work with (the internals) stored/procedures, it wasn't
useful in this case. Also, I keep having to terminate the statement
because it never runs to completion and p
Hi Tom/Adrian.
I should have already stated I did begin with EXPLAIN but given they
don't easily work with (the internals) stored/procedures, it wasn't
useful in this case. Also, I keep having to terminate the statement
because it never runs to completion and produces the plan (at least in
ANALYZE
Adrian Klaver writes:
> On 1/16/24 09:45, Jim Vanns wrote:
>> I have a slow (CPU bound) DELETE statement I'm attempting to debug and I
>> suspect that its actually the ON DELETE CASCADE on the foreign key thats
>> causing it.
99% of the time, the cause is lack of an index on the foreign key's
r
On 1/16/24 09:45, Jim Vanns wrote:
Hi,
I have a slow (CPU bound) DELETE statement I'm attempting to debug and I
suspect that its actually the ON DELETE CASCADE on the foreign key thats
causing it. I suspect this because the dry-run mode of the same query (a
SELECT instead of DELETE) doesn't s
Hi,
I have a slow (CPU bound) DELETE statement I'm attempting to debug and I
suspect that its actually the ON DELETE CASCADE on the foreign key thats
causing it. I suspect this because the dry-run mode of the same query (a
SELECT instead of DELETE) doesn't suffer the same fate. The statement is
ef
