1

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)

View Query:

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.

Improve this question
8
  • What happens if you don't wrap the UNION? pastebin.com/kjGTSH3w
    – user1822
    Aug 31, 2022 at 18:45
  • 1
    @a_horse_with_no_name I get an error: ERROR: subquery in FROM must have an alias LINE 12: (SELECT ^ HINT: For example, FROM (SELECT ...) [AS] foo. Time: 1.891 ms
    – user7097722
    Aug 31, 2022 at 19:58
  • 2
    "(The following queries are pseudo-queries)" Does that mean they don't actually exhibit the behavior in question?
    – jjanes
    Aug 31, 2022 at 19:58
  • @jjanes -- I cannot reveal proprietary information, so this query is an approximation (with different names) of the query I am running.
    – user7097722
    Aug 31, 2022 at 20:00
  • 1
    But that doesn't answer the question. Once you create new tables with different names, and defined your view over them, do they still exhibit the same behavior?
    – jjanes
    Aug 31, 2022 at 20:05

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.