X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 64E579FA1B0 for ; Wed, 10 May 2006 07:43:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 74974-09 for ; Wed, 10 May 2006 07:43:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from h57078.serverkompetenz.net (talk-net.net [81.169.128.8]) by postgresql.org (Postfix) with ESMTP id 96C309FA2FA for ; Wed, 10 May 2006 07:43:13 -0300 (ADT) Received: from [192.168.115.250] (e178141156.adsl.alicedsl.de [85.178.141.156]) by h57078.serverkompetenz.net (Postfix) with ESMTP id 2E6E950C75B; Wed, 10 May 2006 12:41:36 +0200 (CEST) Message-ID: <4461C3D0.4060504@network-inventory.de> Date: Wed, 10 May 2006 12:43:28 +0200 From: Thomas Vatter User-Agent: Mozilla Thunderbird 1.0.5 (Windows/20050711) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tino Wildenhain CC: pgsql-performance@postgresql.org Subject: Re: in memory views References: <4461AFFF.7060200@network-inventory.de> <4461B3DE.6030700@wildenhain.de> <4461B899.4030502@network-inventory.de> <4461BB9E.5030607@wildenhain.de> In-Reply-To: <4461BB9E.5030607@wildenhain.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/235 X-Sequence-Number: 19022 Tino Wildenhain wrote: > Thomas Vatter schrieb: > >> Tino Wildenhain wrote: >> >>> Thomas Vatter schrieb: >>> >>>> is there a possibility for creating views or temp tables in memory >>>> to avoid disk io when user makes select operations? >>> >>> >>> >>> >>> No need. The data will be available in OS and database caches if >>> they are really required often. If not, tune up the caches and >>> do a regular "pre select". >>> >>> Regards >>> Tino >>> >>> >> >> hmm, I am selecting a resultset with 1300 rows joined from 12 tables. >> with jdbc I am waiting 40 seconds until the first row appears. The >> following rows appear really fast but the 40 seconds are a problem. > > > Well you will need the equally 40 seconds to fill your hypothetical > in memory table. (even a bit more due to the creation of a > datastructure). > > So you can do the aproaches of semi materialized views (that are in fact > writing into a shadow table) or just prefetch your data at time - just > at the times you would refill your memory tables if they existed. > A cronjob with select/fetch should do. > > Regards > Tino > > If the in memory table is created a bootup time of the dbms it is already present when user selects the data. Of course the challenge is to keep the in memory table up to date if data are changed. What do you mean with semi materialized views, I have tried select * from this_view with the same result. Also, if I repeat the query it does not run faster. regards tom -- Mit freundlichen Grüßen / Regards Vatter Network Inventory Software Sun Microsystems Principal Partner www.network-inventory.de Tel. 030-79782510 E-Mail thomas.vatter@network-inventory.de