small Partitioned data taking forever in join query

Question

Hi, I have a question regarding the performance of using a partitioned table.
I need to do left join of two tables, lets say A and B. A is 8 GB and B is 60 GB.
I was able to run  the left join in around 4 mins 
```
SELECT 
    *
FROM 
    A 
LEFT JOIN 
   B on A.id = B.id 
```

and I did an experiment creating a partition table filtered_B created by inner join A and B using 
```
CREATE TABLE filtered_B WITH (
	external_location = 's3://my_bucket_name/subquery/filtered_B',
	partitioned_by = ARRAY [ 'year',
	'month' ]
) AS
SELECT 
    B.*,
YEAR(B.delivery_date) AS year,
MONTH(B.delivery_date) AS month
FROM 
    A 
 JOIN 
   B on A.id = B.id
```
the output of table filtered_B turned out to be a lot smaller than original B ---> less than 1 GB

however, if I run  the left join query of A and filtered_B, the below query ran for 30 minutes and eventually timed out.

```
SELECT 
    *
FROM 
  A 
LEFT JOIN 
    filtered_B AS b
ON 
    A.id= b.id
AND
    b.year = 2024 AND b.month = 11
```

I could not understand why this was the case.  I also tried explicitly including the partition in Where statement,
```
SELECT 
*
FROM 
   A
LEFT JOIN 
    (SELECT * FROM filtered_B WHERE year = 2024 AND month = 11) AS b
ON 
    A.id = b.mtid;
```
but it was equally slow and resulted in a  timeout.

I checked the row scanned did get reduced from 70 GB to 2 GB , so really can not understand why the query will timeout on smaller dataset with exactly the same operation.  i do not know if it has anything to do with the way I created partition but i followed the documentation and the data seems to be correctly partitioned in s3 bucket. 
Could anyone help?
thanks in advance!

re:Post Agent · Answer

The issue you're experiencing with the partitioned table's performance is unexpected and counterintuitive, given that the partitioned data is significantly smaller than the original dataset. However, there are several factors that could be contributing to this problem:

1. Partition Pruning: While you've included the partition columns in your WHERE clause, it's possible that the query optimizer isn't effectively using these partitions to prune unnecessary data. To ensure partition pruning is working, make sure you're using the exact partition column names and data types as defined in your table structure.

2. Statistics and Metadata: When you create a new partitioned table, it's crucial to update the table statistics and metadata. Without accurate statistics, the query optimizer might make suboptimal execution plans. Try running MSCK REPAIR TABLE on your partitioned table to update its metadata.

3. Data Distribution: Even though the partitioned table is smaller, the data distribution within it could affect join performance. If the join key (id) is not evenly distributed, it might lead to data skew and slower query execution.

4. Partition Granularity: Having too many small partitions can sometimes lead to performance issues. In your case, partitioning by year and month seems reasonable, but ensure that this granularity is appropriate for your data and query patterns.

5. File Sizes and Formats: The performance could be affected by how the data is stored in S3. Many small files can lead to increased overhead in data retrieval. Consider compacting your data into larger files within each partition to reduce the number of S3 GET requests.

6. Query Optimization: The query optimizer might be choosing a suboptimal execution plan for your partitioned table. You could try rewriting the query using subqueries or CTEs to guide the optimizer.

7. Resource Allocation: Ensure that your Athena query has sufficient resources allocated. If the query is complex or dealing with a large amount of data, it might require more memory or compute power.

To improve performance, consider the following steps:

1. Verify that partition pruning is working by checking the query execution details.
2. Run ANALYZE TABLE to update statistics on your partitioned table.
3. Consider using partition projection if you have a large number of partitions.
4. Optimize your file sizes within partitions, aiming for files between 128MB to 1GB.
5. Experiment with different join strategies or query rewrites to see if they improve performance.
6. Monitor your query execution and resource utilization to identify any bottlenecks.

If these steps don't resolve the issue, you may need to further investigate the data distribution, partition structure, or consult with AWS support for more specific troubleshooting tailored to your environment and use case.  
**Sources**  
[Best practice 10.4 – Partition your data to enable efficient data pruning and reduce unnecessary file reads - Data Analytics Lens](https://docs.aws.amazon.com/wellarchitected/latest/analytics-lens/best-practice-10.4---partition-your-data-to-avoid-unnecessary-file-reads..html)  
[Optimize queries with AWS Glue partition indexing and filtering - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/glue-best-practices-partition-index.html)  
[Partition your data - Amazon Athena](https://docs.aws.amazon.com/athena/latest/ug/partitions.html)  
[Optimizing storage costs and query performance by compacting small objects | AWS Storage Blog](https://aws.amazon.com/blogs/storage/optimizing-storage-costs-and-query-performance-by-compacting-small-objects/)

small Partitioned data taking forever in join query

Add your answer

Relevant content