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.vondra@2ndquadrant.com <mailto:tomas.vondra@2ndquadrant.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