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.
