[GENERAL] Impact of table scan on shared buffers

[Morris Goldstein]

Suppose I have a large table with no indexes, and I scan the entire
thing. What is the impact on the shared buffers? I'm interested in
three scenarios:

- Scan done in SQL using SELECT, (via JDBC if it matters).
- Scan done using SQL COPY.
- Scan done using psql COPY.

I suspect that the SELECT scan will tend to displace everything else
in shared buffers, and I hope that the two forms of COPY do not. Is
that correct?

I'm using 7.4 now, but I'm also interested in the answer for 8.x.

Morris

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Impact of table scan on shared buffers

[Jeff Davis]

On Tue, 2007-11-13 at 17:10 -0500, Morris Goldstein wrote:
> I suspect that the SELECT scan will tend to displace everything else
> in shared buffers, and I hope that the two forms of COPY do not. Is
> that correct?
> 

In 7.4 that may be true, but in later versions it's not.

8.0+ use multiple LRU lists, and a single access of one block won't
evict a frequently-accessed block from cache. That means some of the
blocks in cache will be displaced, but generally not any
frequently-accessed pages.

In 8.3 (currently in beta), a scan of a table larger than
shared_buffers/4 will have almost no impact on shared buffers.

Regards,
Jeff Davis


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly