Any way to push "LIMIT" clause of view further up in execution plan?
I have a rather large reference table R with primary key (C1,C2), and about 50 attribute fields (A1, A2, ... A50). I'd like to build a view on a massive fact table F that joins to R and exposes 3 common attributes; say A2, A4, A6.
Something like [version 1]
CREATE VIEW V AS SELECT F.*, A2, A4, A6 FROM F LEFT OUTER JOIN R USING (C1, C2);
or [version 2]
CREATE VIEW V AS SELECT F.*,
(SELECT A2 FROM R WHERE R.C1=F.C1 AND R.C2=F.C2) A2,
(SELECT A4 FROM R WHERE R.C1=F.C1 AND R.C2=F.C2) A4,
(SELECT A6 FROM R WHERE R.C1=F.C1 AND R.C2=F.C2) A6;
The problem is that many users frequently "sample" such views, with e.g. "SELECT * FROM V LIMIT 100".
For [version 1], so long as a PRIMARY KEY constraint exists on R(C1, C2), the optimizer should be able to
1) restrict the fact-table F to 100 records
2) perform a "lightweight" outer join of those 100 records to the reference table to deliver values of A2, A4, and A6.
Even without PRIMARY KEY constraint, for [version 2] of the view, the optimizer should be able to
1) restrict the fact-table to 100 records
2) perform 3 "lightweight" outer join of those 100 records to the reference table to deliver values of A2, A4, and A6.
In theory, "SELECT * FROM V LIMIT 100" ought to perform just a tad slower than "SELECT * FROM F LIMIT 100".
In practice, I am finding that "SELECT * FROM V LIMIT 100" is performing orders of magnitude slower than "SELECT * FROM F LIMIT 100" - and in [version 2] the more A* subquery-fields from R that are added to the view definition, the more pronounced this becomes.
The execution plan shows why: "SELECT * FROM V LIMIT 100" performs a full (no limit) MATERIALIZE of C1 and C2, then applies the LIMIT on the output of the outer join from F to R.
Is there some design aspect to this schema that I'm overlooking? Either some way that I can set this up so "LIMIT 100" is applied to the fact table before the outer join, or something I've overlooked that tells why the optimizer refuses to do so?
Side note: All statistics / histograms are up-to-date on all tables & projections.