Message-ID: <e16bade2-8942-4027-a1b2-7fea058cecf7@codecat.at>
Date: Mon, 23 Feb 2026 15:18:19 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
To: pgsql-performance@lists.postgresql.org
From: =?UTF-8?Q?Lea_F=C3=BChrer?= <lea@codecat.at>
Subject: MERGE INTO... WHEN NOT MATCHED BY SOURCE index usage
Organization: CodeCat GmbH
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/e16bade2-8942-4027-a1b2-7fea058cecf7%40codecat.at>
Precedence: bulk

Hello!

I've read through the new MERGE documentation with the new WHEN NOT 
MATCHED BY SOURCE functionality and tried it out.

It seems to me like one very common use-case, and one I have bumped into 
often, is to update a n:m resolution table, like this for example:

CREATE TABLE courses (course_id INTEGER PRIMARY KEY);
CREATE TABLE students (student_id INTEGER PRIMARY KEY);
CREATE TABLE students_courses (course_id INTEGER REFERENCES 
courses(course_id), student_id INTEGER REFERENCES students(student_id), 
PRIMARY KEY(student_id,course_id));

/* Insert example data... */
INSERT INTO students
SELECT s FROM generate_series(1,100000) s;
INSERT INTO courses
SELECT c FROM generate_series(1,50) c;

INSERT INTO students_courses (course_id, student_id)
SELECT c, s FROM generate_series(1,50) c, generate_series(1,100000) s;

/* Student nr 5 is only enrolled in courses 7,8,9 */
MERGE INTO students_courses
USING (VALUES (7),(8),(9)) as s(source_course_id) ON 
students_courses.student_id=5 AND students_courses.course_id = 
source_course_id
WHEN NOT MATCHED BY TARGET THEN INSERT VALUES (source_course_id, 5)
WHEN NOT MATCHED BY SOURCE AND student_id=5 THEN DELETE;

SELECT * FROM students_courses
WHERE student_id=5;


The example above works, but looking at the execution plan it does a 
full seq scan on students_course without any filter for student_id.

Do I misunderstand the usage of MERGE WHEN NOT MATCHED BY SOURCE? Is 
this a non intended use-case? Is there another way of defining a filter 
for the MERGE command?

Updating a many-to-many relationship seems like a very common use-case 
to me, and the new MERGE seems perfect for this, but in practice the 
fact it doesn't use an index seems to make it very ineffective 
performance wise for this.