X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0A2AC8B9F5C for ; Tue, 15 Feb 2005 07:07:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82012-02 for ; Tue, 15 Feb 2005 07:07:27 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id D78B08BA1AC for ; Tue, 15 Feb 2005 07:07:26 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1D0wnc-0000YX-00; Tue, 15 Feb 2005 02:07:08 -0500 To: Mark Aufflick Cc: " " Subject: Re: seq scan cache vs. index cache smackdown References: In-Reply-To: From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 15 Feb 2005 02:07:07 -0500 Message-ID: <87d5v247z8.fsf@stark.xeocode.com> Lines: 44 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.056 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/232 X-Sequence-Number: 10561 Mark Aufflick 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