Re: SeqScan costs - Mailing list pgsql-hackers

On Mon, 2008-08-18 at 16:44 +0100, Gregory Stark wrote:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
> 
> > Gregory Stark <stark@enterprisedb.com> writes:
> >>> On Tue, 2008-08-12 at 15:46 -0400, Tom Lane wrote:
> >>>> This is only going to matter for a table of 1 block (or at least
> very
> >>>> few blocks), and for such a table it's highly likely that it's in
> RAM
> >>>> anyway.  So I'm unconvinced that the proposed change represents a
> >>>> better model of reality.
> >
> >> I think the first block of a sequential scan is clearly a random
> access. If
> >> that doesn't represent reality well then perhaps we need to tackle
> both
> >> problems together.
> >
> > The point I was trying to make (evidently not too well) is that
> fooling
> > around with fundamental aspects of the cost models is not something
> that
> > should be done without any evidence.  We've spent ten years getting
> the
> > system to behave reasonably well with the current models, and it's
> quite
> > possible that changing them to be "more accurate" according to a
> > five-minute analysis is going to make things markedly worse overall.
> >
> > I'm not necessarily opposed to making this change --- it does sound
> > kinda plausible --- but I want to see some hard evidence that it
> does
> > more good than harm before we put it in.
> 
> I don't want to see this thread completely drop because it also seems
> pretty
> plausible to me too.
> 
> So what kind of evidence do we need? I'm thinking a query like
> 
> select (select count(*) from 1pagetable) as n1,
>        (select count(*) from 2pagetable) as n2,
>        (select count(*) from 3pagetable) as n3,
>        ...
>   from fairlylargetable
> 
> for various maximum size subquery tables would give an idea of how
> much cpu
> time is spent thrashing through the sequential scans. If we raise the
> cost of
> small sequential scans do the resulting costs get more accurate or do
> they get
> out of whack?

Sounds OK. I've not given up on this yet...

> Perhaps what's also needed here is to measure just how accurate the
> cpu_*
> costs are. Perhaps they need to be raised somewhat if we're
> underestimating
> the cost of digging through 200 tuples on a heap page and the benefit
> of a
> binary search on the index tuples.

Well, that's a can of worms you just opened. I'm trying to suggest a
specific fix to a specific problem.

> >> People lower random_page_cost because we're not doing a good job
> estimating
> >> how much of a table is in cache.
> >
> > Agreed, the elephant in the room is that we lack enough data to
> model
> > caching effects with any degree of realism.
> 
> It looks like we *do* discount the page accesses in
> index_pages_fetched based
> on effective_cache_size. But that's the *only* place we use
> effective_cache_size. We aren't discounting sequential scan or heap
> page
> accesses even when the entire table is much smaller than
> effective_cache_size
> and therefore hopefully cached.

That is about block reuse within the same scan, that's why it only
happens there. It doesn't assume the indexes are already cached, it just
says that they will be if we scan heap blocks in index order.

> We need to think about this. I'm a bit concerned that if we assume
> small tables are always cached 

I've not suggested that and we don't currently assume that.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support