pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Mark Aufflick <mark@pumptheory.com>
Cc: " <pgsql-performance@postgresql.org>
Cc: pgsql-performance@postgresql.org
Cc: " <pgsql-performance@postgresql.org>
Subject: Re: seq scan cache vs. index cache smackdown
Date: 15 Feb 2005 02:07:07 -0500
Message-ID: <87d5v247z8.fsf@stark.xeocode.com> (raw)
In-Reply-To: <FC8ED065-7EE0-11D9-904E-003065D62456@pumptheory.com>
References: <FC8ED065-7EE0-11D9-904E-003065D62456@pumptheory.com>

Mark Aufflick <mark@pumptheory.com> writes:

> Obviously Q2 is faster than Q1, 

That's not really obvious at all. If there are lots of records being returned
the index might not be faster than a sequential scan.

> My assumption is that the sequential scan is blowing the index from any cache
> it might live in, and simultaneously stealing all the disk IO that is needed to
> access the index on disk (the table has 200,000 rows).

It kind of sounds to me like you've lowered random_page_cost to reflect the
fact that your indexes are nearly always completely cached. But when they're
not this unrealistic random_page_cost causes indexes to be used when they're
no longer faster.

Perhaps you should post an "EXPLAIN ANALYZE" of your Q1 and Q2 (the latter
preferable with and without enable_indexscan, but since it's a join you may
not be able to get precisely the comparable plan without just that one index
scan.)

> 2) change the way the server allocates/prioritizes different caches - i don't
> know enough about how postgres caches work to do this (if it's possible)

Postgres keeps one set of shared buffers, not separate pools . Normally you
only allocate a small amount of your memory for Postgres and let the OS handle
disk caching.

What is your shared_buffers set to and how much memory do you have?

> 3) try it on postgres 7.4 - possible, but migrating the system to 7.4 in
> production will be hard because the above code that I am not responsible for
> has a lot of (slightly wacky) implicit date casts

I can't think of any 7.4 changes that would affect this directly, but there
were certainly plenty of changes that had broad effects. you never know. 

8.0, on the other hand, has a new algorithm that specifically tries to protect
against the shared buffers being blown out by a sequential scan. But that will
only help if it's the shared buffers being thrashed that's hurting you, not
the entire OS file system cache.

-- 
greg




view thread (24+ messages)  latest in thread

Message-ID: <87d5v247z8.fsf@stark.xeocode.com>
Permalink:  ../87d5v247z8.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/87d5v247z8.fsf@stark.xeocode.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: gsstark@mit.edu, mark@pumptheory.com
  Subject: Re: seq scan cache vs. index cache smackdown
  In-Reply-To: <87d5v247z8.fsf@stark.xeocode.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox