Re: In-Memory Columnar Store

From: knizhnik <knizhnik(at)garret(dot)ru>
To: "ktm(at)rice(dot)edu" <ktm(at)rice(dot)edu>
Cc: pgsql-hackers(at)postgresql(dot)org, Abhijit Menon-Sen <ams(at)2ndQuadrant(dot)com>, Oleg Bartunov <obartunov(at)gmail(dot)com>
Subject: Re: In-Memory Columnar Store
Date: 2013-12-11 16:25:33
Message-ID: 52A891FD.8090901@garret.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

I depends on what you mean by "transparently substitute".
I f you want to be able to execute standard SQL queries using columnar
store, then it seems to be impossible without rewriting of executor.
I provided another approach based on calling standard functions which
perform manipulations not with scalar types but with timeseries.

For example instead of standard SQL

select sum(ClosePrice) from Quote;

I will have to write:

select cs_sum(ClosePrice) from Quote_get();

It looks similar but not quite the same.
And for more complex queries difference is larger.
For example the query

select sum(score*volenquired)/sum(volenquired) from DbItem group by
(trader,desk,office);

can be written as

select agg_val,cs_cut(group_by,'c22c30c10') from
(select (cs_project_agg(ss1.*)).* from
(select (s1).sum/(s2).sum,(s1).groups from DbItem_get() q,
cs_hash_sum(q.score*q.volenquired,
q.trader||q.desk||q.office) s1,
cs_hash_sum(q.volenquired, q.trader||q.desk||q.office)
s2) ss1) ss2;

Looks too complex, doesn't it?
But first two lines are responsible to perform reverse mapping: from
vertical data representation to normal horisontal tuples.
The good thing is that this query is executed more than 1000 times
faster (with default PostgreSQL configuration parameters except shared
shared_buffers
which was set large enough to fit all data in memory).

On 12/11/2013 07:14 PM, ktm(at)rice(dot)edu wrote:
> On Mon, Dec 09, 2013 at 11:40:41PM +0400, knizhnik wrote:
>> Hello!
>>
>> I want to annouce my implementation of In-Memory Columnar Store
>> extension for PostgreSQL:
>>
>> Documentation: http://www.garret.ru/imcs/user_guide.html
>> Sources: http://www.garret.ru/imcs-1.01.tar.gz
>>
>> Any feedbacks, bug reports and suggestions are welcome.
>>
>> Vertical representation of data is stored in PostgreSQL shared memory.
>> This is why it is important to be able to utilize all available
>> physical memory.
> Hi,
>
> This is very neat! The question I have, which applies to the matview
> support as well, is "How can we transparently substitute usage of the
> in-memory columnar store/matview in a SQL query?".
>
> Regards,
> Ken

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2013-12-11 16:28:33 Re: Why the buildfarm is all pink
Previous Message Andres Freund 2013-12-11 16:25:31 Re: logical changeset generation v6.8