Re: in memory views

Hi,

there was a similar discussion with a ramdisk:
http://archives.postgresql.org/pgsql-hackers/2005-11/msg01058.php

You need to populate the data on serverstart, of course.

But as Timo mentionend, it's maybe not worth the trouble.

Maybe their is a way to speed up the queriy itself.

To analyze this, you should post the query- and table-definition
and the output of explain analyze of the offending query.

Best regards

Hakan Kocaman
Software-Development

digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln

Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan(dot)kocaman(at)digame(dot)de

> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of
> Thomas Vatter
> Sent: Wednesday, May 10, 2006 12:43 PM
> To: Tino Wildenhain
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] in memory views
>
>
> 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(dot)vatter(at)network-inventory(dot)de
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>