Is BRIN used for delete cascade?

Question

I need indexes on some large tables to support ON DELETE CASCADE, but the size of btree indexes would be a problem. I therefore tried with BRIN, since the performance isn't critical. However, it seems they are never used, or at least the deletes are about as slow as without indexes. Could someone confirm that the planner can use BRIN indexes for cascading deletes? Is there a way I could examine the plan?

Using [auto-explain](https://www.postgresql.org/docs/current/auto-explain.html), you can log query plans of triggers etc — Bergi, Aug 17 '21 at 09:56
Would this give me the actual plan for each trigger or just the time? Because I can get the time with EXPLAIN ANALYZE. — Rasmus, Aug 17 '21 at 10:07

score 2 · Accepted Answer · answered Aug 17 '21 at 14:37

Use auto_explain with these settings:

auto_explain.log_min_duration = '0ms'
auto_explain.log_nested_statements=on

and you will get the plan for the cascading delete.

And with this setting, it will get sent to your client and you won't have to go dig it out of the log file:

set client_min_messages = log;

BRIN indexes can be used. But if the table is not suitable for BRIN indexes (the rows are not nearly in order by the key column) using one will not actually be faster.

Is BRIN used for delete cascade?

1 Answers1