Re: FETCH FIRST clause PERCENT option

On 1/24/19 10:57 AM, Surafel Temesgen wrote:
>
>
> On Wed, Jan 9, 2019 at 8:18 PM Tomas Vondra
> <tomas(dot)vondra(at)2ndquadrant(dot)com <mailto:tomas(dot)vondra(at)2ndquadrant(dot)com>> wrote:
>  
>
>
> See the attached patch, which recomputes the count regularly. I don't
> claim the patch is committable or that it has no other bugs, but
> hopefully it shows what I meant.
>
>
>
> I got only one issue it is not work well with cursor
>
> postgres=# START TRANSACTION;
>
> START TRANSACTION
>
> postgres=# create table t as select i from generate_series(1,1000) s(i);
>
> SELECT 1000
>
> postgres=# declare c cursor for select * from t fetch first 5 percent
> rows only;
>
> DECLARE CURSOR
>
> postgres=# fetch all in c;
>
> ERROR: trying to store a minimal tuple into wrong type of slot
>
>
> I am looking at it .
>

OK. Does that mean you agree the incremental approach is reasonable?

> meanwhile i fix row estimation and cost and make create_ordered_paths
> creation with no LIMIT consideration in PERCENTAGE case
>

I haven't reviewed the costing code yet, but linear interpolation seems
reasonable in principle. I find the create_limit_path changes somewhat
sloppy and difficult to comprehend (particularly without comments).

For example, the fact that the code computes the total cost based on
count_rows, which may be tweaked later seems suspicious. I mean, the doe
now does this:

if (limitOption = PERCENTAGE)
{
... update count_rows
... compute total_cost
}

if (count_rows > pathnode->path.rows)
count_rows = pathnode->path.rows;

... compute total_cost for the EXACT_NUMBER case

pathnode->path.rows = count_rows;

But I need to do think about this a bit more.

FWIW I see there are no regression tests for the PERCENT option.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services