Re: [HACKERS] Some notes on optimizer cost estimates

Then <hotz(at)jpl(dot)nasa(dot)gov> spoke up and said:
> >I don't know how to do that --- AFAICS, getting trustworthy numbers by
> >measurement would require hundreds of meg of temporary disk space and
> >probably hours of runtime. (A smaller test would be completely
> >corrupted by kernel disk caching effects.)
>
> Getting a rough estimate of CPU speed is trivial. Getting a rough estimate
> of sequential disk access shouldn't be too hard, though you would need to
> make sure it didn't give the wrong answer if you ran configure twice in a
> row or something. Getting a rough estimate of disk access for a single
> non-sequential disk page also shouldn't be too hard with the same caviats.
> Measuring sequential vs. random reads probably takes a large dataset as you
> say.

A point for consideration: this need not be a configure test. Any
commercial database usage carries with it the expectation of a
non-trivial effort at tuning. This being the case, it might make
sense to bring in some foresight here. As PostgreSQL matures, people
are going to be using it on non-homogeneous systems (e.g mixture of
3600, 7200, and 10k rpm disks). Our cost estimates should therefore
vary somewhat as tables start living on different disks (yet another
reason why symlinks are not the answer).

Right now, I would kill to have a tool that I could run over a couple
hours and many gigabytes of disk space that would give me indications
of how to tune my Oracle database. We may want to think about, in the
future, adding in the ability to tune specific tables by keeping query
statistics and analyzing them. Even post-processing debug output
would help, although turning debugging on adds some non-trivial
overhead.

--
=====================================================================
| JAVA must have been developed in the wilds of West Virginia. |
| After all, why else would it support only single inheritance?? |
=====================================================================
| Finger geek(at)cmu(dot)edu for my public key. |
=====================================================================