Skip to content

Instantly share code, notes, and snippets.

/test.sql Secret
Created Apr 27, 2016

Embed
What would you like to do?
Test: filtering unions
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.