Message-ID: <4461DB55.3030005@network-inventory.de>
Date: Wed, 10 May 2006 14:23:49 +0200
From: Thomas Vatter <thomas.vatter@network-inventory.de>
User-Agent: Mozilla Thunderbird 1.0.5 (Windows/20050711)
MIME-Version: 1.0
To: Tino Wildenhain <tino@wildenhain.de>
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>
	<4461D510.5090806@wildenhain.de>
In-Reply-To: <4461D510.5090806@wildenhain.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tino Wildenhain wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>

The subsequent rows are shown faster not the subsequent queries - if  
you really read my first e-mail ;-) . Yes, I have done analyse 
yesterday, the database has not changed, afaik it is necessary when the 
database contents are changing.

regards
tom