Agfdhyk

Configured the SQL Server connnector in Presto, and tried few simple queries like:

Select count(0) from table_name

or,

Select sum(column_name) from table_name

Both above queries ran in SQL server in 300 ms and in Presto its running over 3 min.

This is the explain analyze of the second query (it seems to do table scan and fetch huge amount of data before doing sum), why it couldnt pushed down the sum operator to SQL Server itself.

    Query Plan                                                       

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Fragment 1 [SINGLE]                                                                                                                                                      

     Cost: CPU 2.98ms, Input: 1 row (9B), Output: 1 row (9B)                                                                                                              

     Output layout: [sum]                                                                                                                                                 

     Output partitioning: SINGLE                                                                                                                                        

     - Aggregate(FINAL) => [sum:double]                                                                                                                                   

             Cost: ?%, Output: 1 row (9B)                                                                                                                                 

             Input avg.: 1.00 lines, Input std.dev.: 0.00%                                                                                                                

             sum := "sum"("sum_4")                                                                                                                                        

         - LocalExchange[SINGLE] () => sum_4:double                                                                                                                       

                 Cost: ?%, Output: 1 row (9B)                                                                                                                             

                 Input avg.: 0.06 lines, Input std.dev.: 387.30%                                                                                                          

             - RemoteSource[2] => [sum_4:double]                                                                                                                          

                     Cost: ?%, Output: 1 row (9B)                                                                                                                         

                     Input avg.: 0.06 lines, Input std.dev.: 387.30%                                                                                                      



 Fragment 2 [SOURCE]                                                                                                                                                      

     Cost: CPU 1.67m, Input: 220770667 rows (1.85GB), Output: 1 row (9B)                                                                                                  

     Output layout: [sum_4]                                                                                                                                               

     Output partitioning: SINGLE                                                                                                                                        

     - Aggregate(PARTIAL) => [sum_4:double]                                                                                                                               

             Cost: 0.21%, Output: 1 row (9B)                                                                                                                              

             Input avg.: 220770667.00 lines, Input std.dev.: 0.00%                                                                                                        

             sum_4 := "sum"("total_base_dtd")                                                                                                                             

         - TableScan[sqlserver:sqlserver:table_name:ivpSQLDatabase:table_name  ..

                 Cost: 99.79%, Output: 220770667 rows (1.85GB)                                                                                                            

                 Input avg.: 220770667.00 lines, Input std.dev.: 0.00%                                                                                                    

                 total_base_dtd := JdbcColumnHandle{connectorId=sqlserver, columnName=total_base_dtd, columnType=double}

Both example queries are aggregate queries that produce single row result.
Currently, in Presto it is not possible to push down an aggregation to the underlying data store. Conditions and column selection (narrowing projections) are pushed down, but aggregations are not.

As a result, when you query SQL Server from Presto, Presto needs to read all the data (from given column) to do the aggregation, so there is a lot of disk and network traffic. Also, it might be, that SQL Server could optimize away certain aggregations so it might be skipping data read at all (i am guessing here).

Presto is not suited to be a frontend to some other database. It can be used as such, but this has some implications. Presto shines when it is put to work as a big data query engine (over S3, HDFS or other object stores) or as a federated query engine, where you combine data from multiple data stores / connectors.

Presto doesn't support aggregate-pushdowns but as a workaround, you can create views in the source database (SQL Server in your case) and query those views from Presto.