Slow sql performance

[talalryz]

Running,

spark.read.table('database.table').limit(10).show()

is a lot faster than running,

spark.sql('SELECT * from database.table limit 10')

Intuitively, we would expect both of these operations to have similar run times. Looking a bit deeper,
it seems that spark.sql is forcing an entire file scan, where as spark.read.table.limit does not. This problem extends all the way to filtering by partition cols as well e.g.,
spark.sql('SELECT * from database.table where partition_col=<value>') also forces a full table scan, while using spark.read.table.filter does not.

Is there something I could be missing, e.g., a spark configuration that could be causing this or is this a known issue?

[pancodia]

Same slow performance for me when using Pyspark on EMR with Glue metastore. But for me there is no difference between the two query methods.

Also found for me if I filter by partition columns, data scan is reduced therefore query speeds up.

[heetu]

Hi @talalryz, and @pancodia, did you get any conclusion?