Select count(*) fails on big amount of data in spark

[iwaniwaniwan012]

I tested hdfs_fdw with spark. In spark i created table from local file with 100M rows. In spark beeline i count with select count(*),but pg server get an error oom, and spark thift server fails too. Pg 9.6, spark 2.2.0, hdfs_fwd 2.0.3

[gabbasb]

Can you please provide the output of the following commands:
beeline : EXPLAIN EXTENDED select count() from big_table;
psql : EXPLAIN VERBOSE select count() from big_table;

Please note that Aggregate push down is not available in hdfs_fdw 2.0.3.

[iwaniwaniwan012]

explain extended select count(*) from test;
| == Parsed Logical Plan ==
'Project [unresolvedalias('count(1), None)]
+- 'UnresolvedRelation test

== Analyzed Logical Plan ==
count(1): bigint
Aggregate [count(1) AS count(1)#28L]
+- MetastoreRelation default, test

== Optimized Logical Plan ==
Aggregate [count(1) AS count(1)#28L]
+- Project
+- MetastoreRelation default, test

== Physical Plan ==
*HashAggregate(keys=[], functions=[count(1)], output=[count(1)#28L])
+- Exchange SinglePartition
+- *HashAggregate(keys=[], functions=[partial_count(1)], output=[count#30L])
+- HiveTableScan MetastoreRelation default, test |

EXPLAIN VERBOSE select count(*) from spark_table ;
QUERY PLAN

Aggregate (cost=1100002.50..1100002.51 rows=1 width=8)
Output: count(*)
-> Foreign Scan on public.spark_table (cost=100000.00..1100000.00 rows=1000 width=0)
Output: id, txt, tm
Remote SQL: SELECT * FROM default.test

[gabbasb]

In case of beeline a map-reduce job will be initiated for doing hash aggregate, in hdfs_fdw case all rows would first get selected which triggers OOM error. This will work when we provide support for pushing down aggregates to the spark/hive server in the remote query.

[iwaniwaniwan012]

Thanks, are you going to provide this support soon or not?