Re: UPDATEDs slowing SELECTs in a fully cached database

lars <lhofhansl@yahoo.com> wrote:

> I am not trying to optimize this particular use case, but rather
> to understand what Postgres is doing, and why SELECT queries are
> affected negatively (sometimes severely) by concurrent (or even
> preceding) UPDATEs at all when the database resides in the cache
> completely.

I think we're stuck in terms of trying to guess what is causing the
behavior you are seeing.  Things which would help get it "unstuck":

(1)  More information about your load process.  Looking at the code,
I could sort of see a possible path to this behavior if the load
process involves any adjustments beyond straight INSERTs or COPYs
in.

(2)  You could poke around with a profiler, a debugger, and/or
the contrib/pageinspect module to sort things out.

(3)  You could post a reproducible test case -- where you start with
CREATE TABLE and populate with something like the generate_series()
function and go through a clearly described set of steps to see the
behavior.   With the someone else could do the profiling, debugging,
and/or page inspection.

From what you've said, it seems like either you're omitting some
detail as irrelevant which is actually significant, or you've found
a bug we should hunt down and fix.  I really don't know which it is.

-Kevin