Re: [feature] cached index to speed up specific queries on extremely large data sets

On Fri, Apr 11, 2014 at 1:26 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 04/11/2014 03:20 PM, lkcl . wrote:
>>
>> so i had an idea.  there already exists the concept of indexes.  there
>> already exists the concept of "cached queries".  question: would it be
>> practical to*merge*  those two concepts such that specific queries
>> could be*updated*  as new records are added, such that when the query
>>
>> is called again it answers basically pretty much immediately? let us
>> assume that performance degradation on "update" (given that indexes
>> already exist and are required to be updated) is acceptable.
>
>
> I think you just described materialized views.
... well... dang :)
http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views
ok so definitely not the snapshot materialised views, but yes!  the
eager materialised views, definitely.

> The built-in materialized
> views in PostgreSQL are not updated immediately as the tables are modified,
... but that would probably be enough.

> but it's entirely possible to roll your own using views and triggers. There
> are a few links on the PostgreSQL wiki, in the "Versions before 9.3"
> section: https://wiki.postgresql.org/wiki/Materialized_Views.
awesome.  uhhh, well that was easy *lol*.  once i am paid, whom do i
send the payment to for the fast response and incredibly valuable
information? :)  [this is a serious question!]

l.