Re: in memory views

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