I have a view but querying it is about 100x slower than running the view's query with the additional WHERE clause. Why is it not flattening and running at comparable speed? I can also see the issue when comparing EXPAIN ANALYZE output for both.
(The following queries are pseudo-queries)
CREATE OR REPLACE VIEW person AS SELECT name, student_id, teacher_id, enrollment_id FROM ( (SELECT CONCAT_WS(' ', name_first, name_last) as name, student_id as student_id, Null as teacher_id, enrollment_id FROM student ) UNION ALL (SELECT full_name as name, Null as student_id, id as teacher_id, enrollment_id FROM teacher) ) as persons
If I run the above query with the addition of a where clause (
WHERE enrollment_id = 1), I get blazing-fast results. If instead I run:
SELECT * FROM person WHERE enrollment_id=1, it takes much, much longer. My "student" table is very large.