Re: FETCH FIRST clause PERCENT option

Surafel,

On Wed, Jul 17, 2019 at 3:45 AM Surafel Temesgen <surafel3000(at)gmail(dot)com>
wrote:

>
> Hi Ryan,
> On Tue, Jul 9, 2019 at 4:13 PM Ryan Lambert <ryan(at)rustprooflabs(dot)com>
> wrote:
>
>>
>> "It is possible for FETCH FIRST N PERCENT to create poorly performing
>> query plans when the N supplied exceeds 50 percent. In these cases query
>> execution can take an order of magnitude longer to execute than simply
>> returning the full table. If performance is critical using an explicit row
>> count for limiting is recommended."
>>
>
> I don’t understand how fetch first n percent functionality can be replaced
> with explicit row count limiting. There may be a way to do it in a client
> side but we can not be sure of its performance advantage
>
>
> regards
>
> Surafel
>
>

I was suggesting a warning in the documentation so users aren't caught
unaware about the performance characteristics. My first version was very
rough, how about adding this in doc/src/sgml/ref/select.sgml?

"Using <literal>PERCENT</literal> is best suited to returning single-digit
percentages of the query's total row count."

The following paragraphs in that same section give suggestions and warnings
regarding LIMIT and OFFSET usage, I think this is more in line with the
wording of those existing warnings.

Other than that, we can rip the clause if it is 100%

You mean if PERCENT=100 it should short circuit and run the query
normally? I like that.
That got me thinking, I didn't check what happens with PERCENT>100, I'll
try to test that soon.

Thanks,
Ryan

>