Test: filtering unions

test.sql

-- three views, partitioning the table by date (not indexed)
CREATE VIEW caselist_oldest AS
    SELECT  *,
            'oldest'::TEXT AS source
      FROM  cases
     WHERE  create_stamp < '1996-01-01';

CREATE VIEW caselist_middle AS
    SELECT  *,
            'middle'::TEXT AS source
      FROM  cases
     WHERE  create_stamp BETWEEN '1996-01-01' AND '2006-01-01';

CREATE VIEW caselist_newest AS
    SELECT  *,
            'newest'::TEXT AS source
      FROM  cases
     WHERE  create_stamp > '2006-01-01';

-- fast query with duplicated joins/filters
      SELECT  c.*
        FROM  caselist_oldest c
        JOIN  case_clients cacl ON cacl.case_id = c.id
       WHERE  cacl.client_id = 12046
    UNION ALL
      SELECT  c.*
        FROM  caselist_middle c
        JOIN  case_clients cacl ON cacl.case_id = c.id
       WHERE  cacl.client_id = 12046
    UNION ALL
      SELECT  c.*
        FROM  caselist_newest c
        JOIN  case_clients cacl ON cacl.case_id = c.id
       WHERE  cacl.client_id = 12046;

-- slow query applying joins/filters to union result
      SELECT  combined.*
        FROM  (
                  SELECT * FROM caselist_oldest
                  UNION ALL
                  SELECT * FROM caselist_middle
                  UNION ALL
                  SELECT * FROM caselist_newest
              ) combined
        JOIN  case_clients cacl ON cacl.case_id = combined.id
       WHERE  cacl.client_id = 12046;