Re: Some ideas for comment

--On Mittwoch, August 24, 2005 16:26:40 -0400 Chris Hoover
<revoohc(at)gmail(dot)com> wrote:

> On 8/24/05, Merlin Moncure <merlin(dot)moncure(at)rcsonline(dot)com> wrote:
>> Linux does a pretty good job of deciding what to cache. I don't think
>> this will help much. You can always look at partial indexes too.
>>
> Yes, but won't this help create the need to store less? If I have
> 1,000.000 rows in a table, but only 4,000 are active, if I move those
> 4 to another table and link the tables via a view, should that not
> help keep the 9,996,000 rows out of the kernel cache (the majority of
> the time at least)?
The kernel caches per page, not per file. It is likely linux only caches
those pages which contain active rows, as long as no statement does a
seq-scan on that table.

To optimize the thing, you could consider to cluster by some index which
sorts by the "activity" of the rows first. That way pages with active rows
are likely to contain more than only 1 active row and so the cache is
utilized better.

Cluster is rather slow however and tables need to be reclustered from time
to time.

Mit freundlichem Gruß
Jens Schicke
--
Jens Schicke j(dot)schicke(at)asco(dot)de
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 Braunschweig Fax 0531/3906-400