CODEWITHSUNDEEP
sqlpostgresqljoinconcurrencymvcc
Kim Beob Woo
Kim Beob Woo

Reputation: 31

If concurrent queries insert records between loops of a nested loop join, can it affect the result of the nested loop join?

I was looking into the nested loop join of Postgres and its variations when I got a question. For the default nested loop join, Postgres scans the inner relation every time for each tuple in the outer (although it is not selected). In the case of a materialized nested loop join, on the other hand, the inner relation is scanned once, uploaded to work memory and rescanned in memory for each tuple in the outer relation.

The question here is whether data can be inserted by another query during a nested loop join's loop.

If data can be inserted, I think the result of materialized nested loop join and nested loop join will be different.

(Actually, I don't really know how loops work in a nested loop join. I'm trying to understand it as an abstract concept, and this might be the case.)

I was helped by the documents below and GPT, but I was not convinced. https://www.interdb.jp/pg/pgsql03/05/01.html

Upvotes: 3

Views: 44

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

Concurrent data modifications cannot influence the result of a query in PostgreSQL, regardless if it uses a nested loop join or not.

At the beginning of each statement (or, if the transaction isolation level is REPEATABLE READ or higher, at the beginning of the first statement in a transaction) PostgreSQL takes a snapshot: it determines which transactions are visible and which not. Transactions that are in progress or in the future are never visible, so their effects cannot influence the query result, even if they commit while the query is still running.

Upvotes: 3

Related Questions