> > outer tuples before actually reading a null tuple. I doubt of > > general shortcut for that. It also seems preventing limit node > > from just using materialized outer. > > > > Sure, if you actually want all tuples, you'll have to execute the outer > plan till completion. But that's not what I'm talking about - what if we > only ever need to read one row from the limit?
We have no choice than once reading all tuples just to find we are to return just one row, since estimator is not guaranteed to be exact as required for this purpose.
> To give you a (admittedly, somewhat contrived and artificial example): > > SELECT * FROM t1 WHERE id IN ( > SELECT id FROM t2 ORDER BY x FETCH FIRST 10 PERCENT ROWS ONLY > ); > > Maybe this example is bogus and/or does not really matter in practice. I > don't know, but I've been unable to convince myself that's the case.
I see such kind of idiom common. Even in the quite simple example above, *we* cannot tell how many tuples the inner should return unless we actually fetch all tuples in t2. This is the same problem with count(*).
The query is equivalent to the folloing one.
SELECT * FROM t1 WHERE id IN ( SELECT id FROM t2 ORDER BY x FETCH FIRST (SELECT ceil(count(*) * 0.1) FROM t2) ROWS ONLY );
This scans t2 twice, but this patch does only one full scan moving another partial scan to tuplestore. We would win if the outer is complex enough.
Okay here is the previous implementation with uptread review comment
included and it also consider OFFSET clause in percentage calculation
