Why is my Athena query doing a full scan on a partitioned table with joins on the partition keys

Hi all,

I have this one query that started costing me way too much, it went unnoticed for almost a year, but now costing me $5k a month.

I have this rather large table, 750GB, with 3 partitions, let's call them colA, colB, colC. I counted the number of partitions for each of those columns and it goes like this respectively (2993, 14030, 16520). There's a hierarchy in those partitions, A -> B -> C. When I query this table, I always use those 3 partitions, in that order (even if that doesn't matter).

My data is HIVE partitioned in S3 and in parquet SNAPPY compression format.

Now, I have a little 3MB fact table that tells me the latest partition added since a certain timestamp. Normally this resolves to 1 or 2 partitions. I use this table to do a RIGHT JOIN with the table above so I can filter out the results just to the partitions of interest. Here goes my interesting experiment and results.

The following query uses the partitions correctly:

WITH filter_result AS(
	SELECT *
	FROM my_fact_table
	WHERE timestamp > 1671633935
)
SELECT *
FROM my_large_table l
RIGHT JOIN filter_result fr ON l.colA = fr.colA
	AND l.colB = fr.colB
	AND l.colC = fr.colC
WHERE l.colA IN ('ABC')
	AND l.colB IN ('DEF')
	AND l.colC IN ('GHI')

but this ones, does a full table scan every time.

WITH filter_result AS(
	SELECT *
	FROM my_fact_table
	WHERE timestamp > 1671633935
)
SELECT *
FROM my_large_table l
RIGHT JOIN filter_result fr ON l.colA = fr.colA
	AND l.colB = fr.colB
	AND l.colC = fr.colC

I have tried pretty much every order of operation possible. Using Athena Engine Version3. I cannot push the filter predicate no matter what I try. I always ends up scanning the 750GB of data, $4 a pop for querying.

I cannot do the version that works, because that's now how my query works, I need the fact table to filter out the results of the second table.

This to me seems like a pretty standard use of a SQL engine. If Athena is not capable of doing this, at the very least it is worrying and makes me rethink my choice of technology when it comes to push this sort of workloads.