I work with parquet files stored in AWS S3 buckets. They are multiple TB in size and partitioned by a numeric column containing integer values between 1 and 200, call it my_partition. I read in and perform compute actions on this data in Databricks with autoscaling turned off.
I have found that reading a specific partition path for my parquet object will take 2 seconds, whereas reading the full parquet path and filtering to the partition will take 6 minutes. Searching for answers I found out about InMemoryFileIndex in this StackOverflow. This makes sense to me why the read is so much faster, if I read s3://my_parquet/my_partition=1/ there is a lot less listing/heuristics to perform on file contents than if I read s3://my_parquet/ and perform .filter(col("my_partition")==1).
Strangely, the computation that immediately follows the read is always slower for the specific partition read object, though the spark instructions should be the same. For example:
# Specific partition read + compute
specific_read = spark.read.parquet("s3://my_parquet/my_partition=1/") # Takes 2 seconds
specific_read.count() # Takes 10 minutes
# Full parquet/filter + compute
general_read = spark.read.parquet("s3://my_parquet/").filter(col("my_partition")==1) # Takes 5 minutes
general_read.count() # Takes 1 minute
It seems like reading the full path and filtering is faster overall but I do not understand why, especially when the read operation is so much faster for the reading specific partitions. Why is one method faster than the other? Is there a "best" way to extract data stored in a specific partition for computing on?