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).groupsfrom 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@rice.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
