Re: again on index usage

Tom wrote:
> > The question is: how does one find the proper value? That is, is it
> > possible to design planner benchmarking utility to aid in tuning
> > Postgres?
>
> The trouble is that getting trustworthy numbers requires huge test
> cases, because you have to swamp out the effects of the kernel's own
> buffer caching. I spent about a week running 24-hour-constant-disk-
> banging experiments when I came up with the 4.0 number we use now,
> and even then I didn't feel that I had a really solid range of test
> cases to back it up.

Well, I certainly think you did a great job on your test cases :-)
If you look at Daniel's idle system he has a measured factor of ~4.8.

The number is also (imho correctly) rather an underestimate than an
overestimate. That is, I haven't been able to empirically proove mlw's
point about modern disks not beeing sensitive to scan with larger blocks
vs. random 8k.
(My tests typically used raw devices circumventing the OS completely,
since I do the tests for Informix servers)

> My advice to you is just to drop it to 2.0 and see if you like the plans
> you get any better.

Yup, maybe even lower. I cannot see a difference in disk IO troughput
during seq or index scan in Daniel's test case during his normal workload test.
This is because his system had a CPU bottleneck during his normal workload
test. (Shows again how bad OS's distribute processes (1 CPU serves 2 backends
that have a CPU bottleneck, 1 CPU is idle))

Andreas