Re: FETCH FIRST clause PERCENT option

Hi Ryan,
On Tue, Jul 9, 2019 at 1:27 AM Ryan Lambert <ryan(at)rustprooflabs(dot)com> wrote:

> Hi Surafel,
>
> The v5 patch [1] applies cleanly and passes make installcheck-world.
>
> My concern with this is its performance. As a user I would expect using
> this feature to enable queries to run faster than they would simply pulling
> the full table. I tested on tables ranging from 10k rows up to 10 million
> with the same basic result that using FETCH FIRST N PERCENT is slower than
> using the full table. At best it ran slightly slower than querying the
> full table, at worst it increased execution times by 1400% when using a
> large high percentage (95%).
>
>

The cost of FITCH FIRST N PERCENT execution in current implementation is
the cost of pulling the full table plus the cost of storing and fetching
the tuple from tuplestore so it can not perform better than pulling the
full table in any case . This is because we can't determined the number of
rows to return without executing the plan until the end. We can find the
estimation of rows that will be return in planner estimation but that is
not exact.

regards

Surafel