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 A902D9FA2FA for ; Wed, 10 May 2006 08:57:38 -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 88826-01 for ; Wed, 10 May 2006 08:57:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from service-web.de (p15093784.pureserver.info [217.160.106.224]) by postgresql.org (Postfix) with ESMTP id 470889FA1B0 for ; Wed, 10 May 2006 08:57:34 -0300 (ADT) Received: from [10.100.1.50] (074-016-066-080.eggenet.de [80.66.16.74]) by service-web.de (Postfix) with ESMTP id 575B720002F; Wed, 10 May 2006 13:57:33 +0200 (CEST) Message-ID: <4461D510.5090806@wildenhain.de> Date: Wed, 10 May 2006 13:57:04 +0200 From: Tino Wildenhain User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050929) X-Accept-Language: de-DE, de, en-us, en MIME-Version: 1.0 To: Thomas Vatter 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> <4461C3D0.4060504@network-inventory.de> In-Reply-To: <4461C3D0.4060504@network-inventory.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/238 X-Sequence-Number: 19025 Thomas Vatter schrieb: > Tino Wildenhain wrote: ... >> 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. > Semi materialized views are just views with aditional rules and some triggers which copy data to another table. There are several receipes if you google accordingly. I do not know what you mean by "bootup time" - do you really reboot your database server? *hehe* just kidding ;) In your first email you told me your query indeed runs faster the 2nd time (due to the caching) now you are telling me that it is not. Btw, judging from your analyze output you are using very cryptic table and column names - you can use aliasing in the query and dont have to resort to tiny tags when you actually name the objects ;) Maybe others have comments on your query. Btw, better use explain analyze to get realistic results. Regards Tino