Re: SeqScan costs - Mailing list pgsql-hackers

"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(*)
from3pagetable) 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?

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.

>> 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.

We need to think about this. I'm a bit concerned that if we assume small
tables are always cached that we'll run into problems on the poor but common
schema design that has hundreds of tiny tables. But that seems like a narrow
use case and not worth assuming we *never* get any cache effects on sequential
scans at all.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about
EnterpriseDB'sPostgreSQL training!