Re: FETCH FIRST clause PERCENT option - Mailing list pgsql-hackers

Hi,

I've been looking at this patch today, and I think there's a couple of
issues with the costing and execution. Consider a simple table with 1M rows:

  create table t as select i from generate_series(1,1000000) s(i);

and these two queries, that both select 1% of rows

  select * from t fetch first 10000 rows only;

  select * from t fetch first 1 percent rows only;

which are costed like this

  test=# explain select * from t fetch first 10000 rows only;
                             QUERY PLAN
  -----------------------------------------------------------------
   Limit  (cost=0.00..144.25 rows=10000 width=4)
     ->  Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)
  (2 rows)

  test=# explain select * from t fetch first 1 percent rows only;
                             QUERY PLAN
  -----------------------------------------------------------------
   Limit  (cost=0.00..14425.00 rows=1000000 width=4)
     ->  Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)
  (2 rows)

There are two issues with the costs in the "percent" case.

Firstly, the estimated number of rows should be about the same (10k) in
both cases, but the "percent" case incorrectly uses the total row count
(i.e. as if 100% rows were returned).

It's correct that the total cost for the "percent" case is based on 100%
of rows, of course, because the code has to fetch everything and stash
it into the tuplestore in LIMIT_INITIAL phase.

But that implies the startup cost for the "percent" case can't be 0,
because all this work has to be done before emitting the first row.

So these costing aspects need fixing, IMHO.


The execution part of the patch seems to be working correctly, but I
think there's an improvement - we don't need to execute the outer plan
to completion before emitting the first row. For example, let's say the
outer plan produces 10000 rows in total and we're supposed to return the
first 1% of those rows. We can emit the first row after fetching the
first 100 rows, we don't have to wait for fetching all 10k rows.