> On Jan 18, 2024, at 9:46 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx > > >
