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
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 |