Delegate queries to source system

[Mark Pasterkamp <ma...@gmail.com>]

Dear all,

I have setup calcite to use postgres as a datasource. Right now I am
running into an out of memory exception while executing the following
query: "select * from table order by id limit 10". Checking the log of
postgres it seems like calcite is wanting to first load all data into the
memory (since it is executing "select * from table") and then sort it to
only retain 10 elements.

My machine can't load this table into main memory so I was wondering
whether it is possible to delegate this query to postgres. I have found on
the wiki [1] that it is possible to push down operations. I assume that
this can do the trick but I am not really sure how to implement this. Am I
supposed to create multiple FilterableTables for when these queries are
executed or is it better to create a new plan rule which matches on a sort
- projection - tablescan? And in the latter case, how can I make sure that
it is not calcite handeling these operations? Would transforming them to
their equivalent JdbcRules [2] do the trick? I am slightly confused by the
meaning of a "jdbc calling convention".

And Finally, if this does indeed do what I hope it does (push them down to
postgres in my case), how can I make sure that the planner uses this rule
to rewrite these queries to push down most of these memory expensive
queries to postgres?


Mark


[1]
https://calcite.apache.org/docs/adapter.html#pushing-operations-down-to-your-table
[2]
https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java

Re: Delegate queries to source system

[Muhammad Gelbana <m....@gmail.com>]

To push down predicates and projectsions (selected columns), checkout the
CSV example project, it has an example about how to push down those.
To push down more than that, checkout the Druid adapter example.

I'm not sure about what I'm about to say but I beleive it's possible to
push down the whole query if you convert the plan nodes to the JDBC
convention and somehow configuring calcite to use the PostgreSQL dialect.
Please tell me if this, or anywhere near what I said, worked for you and
how you did it. I remember seeing a similar behavior with Apache Drill
which uses Apache Calcite.

Thanks,
Gelbana


On Tue, Apr 16, 2019 at 3:24 PM Mark Pasterkamp <
markpasterkamp1994@gmail.com> wrote:

> Dear all,
>
> I have setup calcite to use postgres as a datasource. Right now I am
> running into an out of memory exception while executing the following
> query: "select * from table order by id limit 10". Checking the log of
> postgres it seems like calcite is wanting to first load all data into the
> memory (since it is executing "select * from table") and then sort it to
> only retain 10 elements.
>
> My machine can't load this table into main memory so I was wondering
> whether it is possible to delegate this query to postgres. I have found on
> the wiki [1] that it is possible to push down operations. I assume that
> this can do the trick but I am not really sure how to implement this. Am I
> supposed to create multiple FilterableTables for when these queries are
> executed or is it better to create a new plan rule which matches on a sort
> - projection - tablescan? And in the latter case, how can I make sure that
> it is not calcite handeling these operations? Would transforming them to
> their equivalent JdbcRules [2] do the trick? I am slightly confused by the
> meaning of a "jdbc calling convention".
>
> And Finally, if this does indeed do what I hope it does (push them down to
> postgres in my case), how can I make sure that the planner uses this rule
> to rewrite these queries to push down most of these memory expensive
> queries to postgres?
>
>
> Mark
>
>
> [1]
>
> https://calcite.apache.org/docs/adapter.html#pushing-operations-down-to-your-table
> [2]
>
> https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRules.java
>