Re: Startup cost of sequential scan

On Thu, Aug 30, 2018 at 5:05 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru> writes:
> > But I think there is another issue in sequential scan cost. We have
> > zero startup cost for sequential scan. But why?
>
> Because it's what the mental model of startup cost says it should be.

Right. So as I understand. The model is that we start sequential
scan immediately, and then find one row uniformly distributed over the
whole table.

From this model we make a conclusion that we're starting getting rows
from sequential scan sooner than from index scan. And this conclusion
doesn't reflect reality. If even estimates for join with t2 wouldn't
be wrong, then our plan for LIMIT query would be still bad, because it
would be still faster to get that one row using index scan rather than
sequential scan.

So, if understand the mental model correctly, our cost estimate for
LIMIT query is based on the idea that we need to fetch only *fraction*
of row from t1 in order to get 100 resulting rows. This is obviously
wrong, because we're always dealing with whole rows :) But I can't
imagine how we can take care of it without redesigning our whole
costing model...

> Also, I do not think we can change that without a whole lot of unpleasant
> side effects on cases that work well today. Have you even checked to
> see how much of the regression tests break with this change?

I though it's to early to discuss this. But yet, I've checked this.
It appears to be surprisingly few broken tests. Just some reordering
of tables in partition_join, select_parallel.------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company