I have some conflict views:
First, I create some base views to do the low level filters
CREATE OR REPLACE VIEW view1 AS
SELECT * FROM table1 WHERE conditions
CREATE OR REPLACE VIEW view2 AS
SELECT * FROM table2 WHERE conditions
...
Then, I join them respective. The connection relationship looks like one table to many tables, so I create one view for each table-table connection.
CREATE OR REPLACE VIEW join1_view1 AS
SELECT * FROM view1 JOIN view2 ON view1.entity_type = 'View2' AND view1.entity_id = view2.id WHERE conditions
CREATE OR REPLACE VIEW join1_view2 AS
SELECT * FROM view1 JOIN view3 ON view1.entity_type = 'View3' AND view1.entity_id = view3.id WHERE conditions
CREATE OR REPLACE VIEW join2_view1 AS
SELECT * FROM view1 JOIN view4 ON view1.assigned_type = 'View4' AND view1.assigned_id = view4.id WHERE conditions
CREATE OR REPLACE VIEW join2_view2 AS
SELECT * FROM view1 JOIN view5 ON view1.assigned_type = 'View5' AND view1.assigned_id = view5.id WHERE conditions
...
Then I use Union to combine them.
CREATE OR REPLACE VIEW union1 AS
SELECT * FROM join1_view1
UNION
SELECT * FROM join1_view2
CREATE OR REPLACE VIEW union2 AS
SELECT * FROM join2_view1
UNION
SELECT * FROM join2_view2
That's all good for now. But at next step, I will join these two union views.
CREATE OR REPLACE VIEW join_union AS
SELECT * FRON union1 INNER JOIN union2 ON union1.id = union2.id
Then I found if I set different limits, the query time have a bit different.
No limit:
SELECT * FROM join_union
takes 4 secs. Limit 100:
SELECT * FROM join_union LIMIT 100
takes 5 secs. Limit 500 takes 10 secs. LIMIT 1000 takes 16 secs.
It's not finished. After 1000, surprised, LIMIT 2000 takes 1 secs. LIMIT 100, 000 (actually it has total 20, 000 records) takes 3 secs.
The curve looks like:
And the Maximum time happened at LIMIT 1325
I executed EXPLAIN(ANALYSE, BUFFERS) in these cases, and I get these plans:
SELECT * FROM join_union no limit.