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 E735E8BA304 for ; Tue, 15 Feb 2005 05:50:46 +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 61767-05 for ; Tue, 15 Feb 2005 05:50:33 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id 7A33B8BA31C for ; Tue, 15 Feb 2005 05:50:10 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id BF3CD31E3D; Tue, 15 Feb 2005 06:50:09 +0100 (MET) From: Christopher Browne X-Newsgroups: pgsql.performance Subject: Re: seq scan cache vs. index cache smackdown Date: Mon, 14 Feb 2005 23:54:46 -0500 Organization: cbbrowne Computing Inc Lines: 57 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4 (Corporate Culture, linux) Cancel-Lock: sha1:mzUVVPC1lfxpYqCsTEuCiSqli9U= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.331 tagged_above=0 required=5 tests=AWL, INFO_TLD X-Spam-Level: X-Archive-Number: 200502/229 X-Sequence-Number: 10558 The world rejoiced as mark@pumptheory.com (Mark Aufflick) wrote: > Hi All, > > I have boiled my situation down to the following simple case: > (postgres version 7.3) > > * Query 1 is doing a sequential scan over a table (courtesy of field > ILIKE 'foo%') and index joins to a few others > * Query 2 is doing a functional index scan over the same table > (lower(field) LIKE 'foo%') and index joins to a few others > * neither query has an order by clause > * for the purpose of testing, both queries are designed to return the > same result set > > Obviously Q2 is faster than Q1, but if I ever run them both at the > same time (lets say I run two of Q1 and one of Q2 at the same time) > then Q2 consistently returns WORSE times than Q1 (explain analyze > confirms that it is using the index). > > 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). There's something to be said for that... > If I simplify the case to not do the index joins (ie. operate on the > one table only) the situation is not as dramatic, but similar. > > My thoughts are: > > 1) kill the sequential scan - but unfortunately I don't have direct > control over that code This is a good choice, if plausible... > 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) That's what the 8.0 cache changes did... Patent claim issues are leading to some changes to the prioritization, which is liable to change 8.0.something and 8.1. > 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 Moving to 7.4 wouldn't materially change the situation; you'd have to go all the way to version 8. -- (format nil "~S@~S" "cbbrowne" "gmail.com") http://linuxdatabases.info/~cbbrowne/postgresql.html Rules of the Evil Overlord #32. "I will not fly into a rage and kill a messenger who brings me bad news just to illustrate how evil I really am. Good messengers are hard to come by."