How to know wether a DELETE CASCADE will delete child records?

Question

Lets say that I have a table A which is referenced by table B. Each record in table A may be referenced by multiple records from table B. This would be the classic "A has many B".

Table B has a foreign key to table A that specifies ON DELETE CASCADE, so when a record from table A is deleted, the referencing records from table B are deleted as well.

What I am trying to understand is how would it behave when deleting multiple records from parent A. Would the database run a single delete query for every orphaned record in table B? Or would it run a delete query for each deleted record from table A?

I guess that what I am asking is if I delete 100 records from table A would it result in 2 queries (1 for table A and another for table B) or in 101 queries (1 for table A and another one for each deleted record in that table).

I am trying to find this information both in Postgres and SQLite documentation but I can't find anything.

Answer 1

Currently (postgresql 17) foreign key checks and actions in postgresql are implemented as a for each row system trigger.

So deleting 100 rows from a table will perform 100 independent internal queries of the foreign key linked table:

DELETE FROM [ONLY] <fktable> WHERE $1 = fkatt1 [AND ...]