X-Original-To: pgsql-admin@postgresql.org Received: from h-e.com (wan-vc8fa2d.ston.biz.mindspring.com [216.135.168.77]) by postgresql.org (Postfix) with ESMTP id 00669475ADE for ; Wed, 5 Mar 2003 13:11:17 -0500 (EST) Received: from [192.168.42.42] (HELO h-e.com) by h-e.com (CommuniGate Pro SMTP 3.5.9) with ESMTP id 93454 for pgsql-admin@postgresql.org; Wed, 05 Mar 2003 10:11:17 -0800 Date: Wed, 5 Mar 2003 10:11:17 -0800 Mime-Version: 1.0 (Apple Message framework v551) Content-Type: text/plain; charset=US-ASCII; format=flowed Subject: Performance question From: Bob Smith To: pgsql-admin Content-Transfer-Encoding: 7bit Message-Id: X-Mailer: Apple Mail (2.551) X-Archive-Number: 200303/36 X-Sequence-Number: 7684 When I execute a query on a new connection, the performance is many times slower than if the query is repeated. In other words, if I start psql, execute the query, then repeat it immediately, the second time it takes only about 20% as long to execute. Now here's the confusing part, if I exit psql then start it up again, the same thing will occur on the new connection as well, the first execution takes 5x as long again. I don't understand this, it would make sense to me that the second execution being faster is due to disk caching on the server, but then why is it slower again on every new connection? Disk caching should benefit all current and new connections until the cache is flushed, which on this server shouldn't happen for a long time, the load is light and it has lots of RAM. Is Postgres doing some kind of caching itself that lasts only for the life of one backend process? If so, is there any way to make this caching persistent across backends? Server particulars: Postgres 7.2.1, Mac OS X Server 10.1.5, dual 1GHz CPUs, 1.5GB memory Thanks! Bob Smith Hammett & Edison, Inc. bsmith@h-e.com