pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedTips on troubleshooting slow DELETE (suspect cascades)
9+ messages / 5 participants
[nested] [flat]
* Tips on troubleshooting slow DELETE (suspect cascades)
@ 2024-01-16 17:45 Jim Vanns <jvanns@ilm.com>
0 siblings, 1 reply; 9+ messages in thread
From: Jim Vanns @ 2024-01-16 17:45 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
effectively;
# Dry mode
SELECT prune_function(timestamp);
# Destructive mode
DELETE FROM foobar p USING prune_function(timestamp) AS e WHERE p.id =
e.prune_id
The logs seem to hold no information on the progress of the statement but
the CPU is pegged at 100% for hours. The SELECT equivalent runs in under a
minute.
What I need is a way to see into this statement as it executes to confirm
my suspicion - does anyone have any tips on that?
Cheers
Jim
--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Tips on troubleshooting slow DELETE (suspect cascades)
@ 2024-01-16 17:53 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Jim Vanns <jvanns@ilm.com>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2024-01-16 17:53 UTC (permalink / raw)
To: Jim Vanns <jvanns@ilm.com>; pgsql-general@lists.postgresql.org
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 suffer the same fate. The statement is
> effectively;
>
> # Dry mode
> SELECT prune_function(timestamp);
> # Destructive mode
> DELETE FROM foobar p USING prune_function(timestamp) AS e WHERE p.id
> <http://p.id; = e.prune_id
>
> The logs seem to hold no information on the progress of the statement
> but the CPU is pegged at 100% for hours. The SELECT equivalent runs in
> under a minute.
>
> What I need is a way to see into this statement as it executes to
> confirm my suspicion - does anyone have any tips on that?
Explain:
https://www.postgresql.org/docs/current/sql-explain.html
It would also be helpful to reply with the table definitions for the
tables. If that is not possible then at least whether there is an index
on the FK reference in the child table(s)?
>
> Cheers
>
> Jim
>
> --
> Jim Vanns
> Principal Production Engineer
> Industrial Light & Magic, London
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Tips on troubleshooting slow DELETE (suspect cascades)
@ 2024-01-16 22:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2024-01-16 22:16 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Jim Vanns <jvanns@ilm.com>; pgsql-general@lists.postgresql.org
Adrian Klaver <adrian.klaver@aklaver.com> 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
referencing columns. We make you have a unique index on the
referenced columns, because otherwise the FK constraint's semantics
are unclear. But you're not required to make one on the other side.
>> What I need is a way to see into this statement as it executes to
>> confirm my suspicion - does anyone have any tips on that?
> Explain:
> https://www.postgresql.org/docs/current/sql-explain.html
Specifically, if EXPLAIN ANALYZE shows a lot of time spent in the
enforcement trigger for the FK, this is likely what's happening.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Tips on troubleshooting slow DELETE (suspect cascades)
@ 2024-01-18 16:37 Jim Vanns <jvanns@ilm.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 9+ messages in thread
From: Jim Vanns @ 2024-01-18 16:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
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 VERBOSE mode anyway).
I have, however, pulled the function body code out and produced an
isolated case that can be EXPLAINED. The table in question is a
curious one since it models a hierarchy as an adjacency list and so
the fkey reference is back to itself (to a primary key - so is an
additional index required?):
CREATE TABLE tree (
ts TIMESTAMPTZ NOT NULL
tree_id BIGINT NOT NULL,
parent_id BIGINT NULL,
--
CONSTRAINT cstr_tree_pky PRIMARY KEY (tree_id) INCLUDE (parent_id),
FOREIGN KEY (parent_id) REFERENCES tree(tree_id)
ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
);
CREATE INDEX ON tree USING BRIN (ts);
The tree table has 95,915,630 rows.
I've not yet got a complete or reliable plan :( I have made a DB copy
and will be dropping the constraint to see what effect that has.
Cheers,
Jim
On Tue, 16 Jan 2024 at 22:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Adrian Klaver <adrian.klaver@aklaver.com> 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
> referencing columns. We make you have a unique index on the
> referenced columns, because otherwise the FK constraint's semantics
> are unclear. But you're not required to make one on the other side.
>
> >> What I need is a way to see into this statement as it executes to
> >> confirm my suspicion - does anyone have any tips on that?
>
> > Explain:
> > https://www.postgresql.org/docs/current/sql-explain.html
>
> Specifically, if EXPLAIN ANALYZE shows a lot of time spent in the
> enforcement trigger for the FK, this is likely what's happening.
>
> regards, tom lane
--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Tips on troubleshooting slow DELETE (suspect cascades)
@ 2024-01-18 16:46 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Jim Vanns <jvanns@ilm.com>
2 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2024-01-18 16:46 UTC (permalink / raw)
To: Jim Vanns <jvanns@ilm.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org
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 produces the plan (at least in
> ANALYZE VERBOSE mode anyway).
>
> I have, however, pulled the function body code out and produced an
> isolated case that can be EXPLAINED. The table in question is a
I'm guessing the function is prune_function(timestamp)?
What is the function body code?
> curious one since it models a hierarchy as an adjacency list and so
> the fkey reference is back to itself (to a primary key - so is an
> additional index required?):
>
> CREATE TABLE tree (
> ts TIMESTAMPTZ NOT NULL
> tree_id BIGINT NOT NULL,
> parent_id BIGINT NULL,
> --
> CONSTRAINT cstr_tree_pky PRIMARY KEY (tree_id) INCLUDE (parent_id),
> FOREIGN KEY (parent_id) REFERENCES tree(tree_id)
> ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
> );
> CREATE INDEX ON tree USING BRIN (ts);
>
> The tree table has 95,915,630 rows.
>
> I've not yet got a complete or reliable plan :( I have made a DB copy
> and will be dropping the constraint to see what effect that has.
>
> Cheers,
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Tips on troubleshooting slow DELETE (suspect cascades)
@ 2024-01-18 16:53 Rob Sargent <robjsargent@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 9+ messages in thread
From: Rob Sargent @ 2024-01-18 16:53 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Jim Vanns <jvanns@ilm.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org
> On Jan 18, 2024, at 9:46 AM, Adrian Klaver <adrian.klaver@aklaver.com> 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 having to terminate the statement
>> because it never runs to completion and produces the plan (at least in
>> ANALYZE VERBOSE mode anyway).
>> I have, however, pulled the function body code out and produced an
>> isolated case that can be EXPLAINED. The table in question is a
>
> I'm guessing the function is prune_function(timestamp)?
>
> What is the function body code?
>
>> curious one since it models a hierarchy as an adjacency list and so
>> the fkey reference is back to itself (to a primary key - so is an
>> additional index required?):
>> CREATE TABLE tree (
>> ts TIMESTAMPTZ NOT NULL
>> tree_id BIGINT NOT NULL,
>> parent_id BIGINT NULL,
>> --
>> CONSTRAINT cstr_tree_pky PRIMARY KEY (tree_id) INCLUDE (parent_id),
>> FOREIGN KEY (parent_id) REFERENCES tree(tree_id)
>> ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
>> );
>> CREATE INDEX ON tree USING BRIN (ts);
>> The tree table has 95,915,630 rows.
>> I've not yet got a complete or reliable plan :( I have made a DB copy
>> and will be dropping the constraint to see what effect that has.
>> Cheers,
>
Have you tried making a table from prune_function() and using that in the delete statement?
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Tips on troubleshooting slow DELETE (suspect cascades)
@ 2024-01-18 17:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim Vanns <jvanns@ilm.com>
2 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2024-01-18 17:01 UTC (permalink / raw)
To: Jim Vanns <jvanns@ilm.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
Jim Vanns <jvanns@ilm.com> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Tips on troubleshooting slow DELETE (suspect cascades)
@ 2024-01-18 17:03 Jim Vanns <jvanns@ilm.com>
parent: Jim Vanns <jvanns@ilm.com>
2 siblings, 1 reply; 9+ messages in thread
From: Jim Vanns @ 2024-01-18 17:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
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 <jvanns@ilm.com> 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 produces the plan (at least in
> ANALYZE VERBOSE mode anyway).
>
> I have, however, pulled the function body code out and produced an
> isolated case that can be EXPLAINED. The table in question is a
> curious one since it models a hierarchy as an adjacency list and so
> the fkey reference is back to itself (to a primary key - so is an
> additional index required?):
>
> CREATE TABLE tree (
> ts TIMESTAMPTZ NOT NULL
> tree_id BIGINT NOT NULL,
> parent_id BIGINT NULL,
> --
> CONSTRAINT cstr_tree_pky PRIMARY KEY (tree_id) INCLUDE (parent_id),
> FOREIGN KEY (parent_id) REFERENCES tree(tree_id)
> ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
> );
> CREATE INDEX ON tree USING BRIN (ts);
>
> The tree table has 95,915,630 rows.
>
> I've not yet got a complete or reliable plan :( I have made a DB copy
> and will be dropping the constraint to see what effect that has.
>
> Cheers,
>
> Jim
>
> On Tue, 16 Jan 2024 at 22:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > Adrian Klaver <adrian.klaver@aklaver.com> 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
> > referencing columns. We make you have a unique index on the
> > referenced columns, because otherwise the FK constraint's semantics
> > are unclear. But you're not required to make one on the other side.
> >
> > >> What I need is a way to see into this statement as it executes to
> > >> confirm my suspicion - does anyone have any tips on that?
> >
> > > Explain:
> > > https://www.postgresql.org/docs/current/sql-explain.html
> >
> > Specifically, if EXPLAIN ANALYZE shows a lot of time spent in the
> > enforcement trigger for the FK, this is likely what's happening.
> >
> > regards, tom lane
>
>
>
> --
> Jim Vanns
> Principal Production Engineer
> Industrial Light & Magic, London
--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Tips on troubleshooting slow DELETE (suspect cascades)
@ 2024-01-18 19:26 Martin Ritchie <martinritchie@geotab.com>
parent: Jim Vanns <jvanns@ilm.com>
0 siblings, 0 replies; 9+ messages in thread
From: Martin Ritchie @ 2024-01-18 19:26 UTC (permalink / raw)
To: Jim Vanns <jvanns@ilm.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
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 <jvanns@ilm.com> wrote:
> 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 <jvanns@ilm.com> 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 produces the plan (at least in
> > ANALYZE VERBOSE mode anyway).
> >
> > I have, however, pulled the function body code out and produced an
> > isolated case that can be EXPLAINED. The table in question is a
> > curious one since it models a hierarchy as an adjacency list and so
> > the fkey reference is back to itself (to a primary key - so is an
> > additional index required?):
> >
> > CREATE TABLE tree (
> > ts TIMESTAMPTZ NOT NULL
> > tree_id BIGINT NOT NULL,
> > parent_id BIGINT NULL,
> > --
> > CONSTRAINT cstr_tree_pky PRIMARY KEY (tree_id) INCLUDE (parent_id),
> > FOREIGN KEY (parent_id) REFERENCES tree(tree_id)
> > ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
> > );
> > CREATE INDEX ON tree USING BRIN (ts);
> >
> > The tree table has 95,915,630 rows.
> >
> > I've not yet got a complete or reliable plan :( I have made a DB copy
> > and will be dropping the constraint to see what effect that has.
> >
> > Cheers,
> >
> > Jim
> >
> > On Tue, 16 Jan 2024 at 22:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > >
> > > Adrian Klaver <adrian.klaver@aklaver.com> 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
> > > referencing columns. We make you have a unique index on the
> > > referenced columns, because otherwise the FK constraint's semantics
> > > are unclear. But you're not required to make one on the other side.
> > >
> > > >> What I need is a way to see into this statement as it executes to
> > > >> confirm my suspicion - does anyone have any tips on that?
> > >
> > > > Explain:
> > > > https://www.postgresql.org/docs/current/sql-explain.html
> > >
> > > Specifically, if EXPLAIN ANALYZE shows a lot of time spent in the
> > > enforcement trigger for the FK, this is likely what's happening.
> > >
> > > regards, tom lane
> >
> >
> >
> > --
> > Jim Vanns
> > Principal Production Engineer
> > Industrial Light & Magic, London
>
>
>
> --
> Jim Vanns
> Principal Production Engineer
> Industrial Light & Magic, London
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-01-18 19:26 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-16 17:45 Tips on troubleshooting slow DELETE (suspect cascades) Jim Vanns <jvanns@ilm.com>
2024-01-16 17:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-01-16 22:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-18 16:37 ` Jim Vanns <jvanns@ilm.com>
2024-01-18 16:46 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-01-18 16:53 ` Rob Sargent <robjsargent@gmail.com>
2024-01-18 17:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-18 17:03 ` Jim Vanns <jvanns@ilm.com>
2024-01-18 19:26 ` Martin Ritchie <martinritchie@geotab.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox