Understanding data scanned when querying ORC with Presto/Athena

Question

I have a large amount of data in ORC files in AWS S3. The data in ORC files is sorted by uuid. I create an AWS Athena (Presto) table on top of them and run the following experiment.

First, I retrieve the first row to see how much data gets scanned:

select * from my_table limit 1

This query reports 18 MB of data being scanned.

I record the uuid from the row returned from the first query and run the following query:

select * from my_table where uuid= limit 1

This query reports 8.5 GB of data being scanned.

By design, both queries return the same result but the second query scans 500 times more data!

Any ideas why this is happening? Is this something inherent to ORC design or is it specific to how Presto interacts with S3?

[EDIT after ilya-kisil's response]

Let's change the last query to only select the uuid column:

select uuid from my_table where uuid= limit 1

For this query, the amount of data scanned drops to about 600 MB! This means that the bulk of the 8.5 GB scanned in the second query is attributed to gathering values from all columns for the record found and not to finding this record.

Given that all values in the record add up to no more than 1 MB, scanning almost 8 GB of data to put these values together seems extremely excessive. This seems like some idiosyncrasy of ORC or columnar formats in general and I am wondering if there are standard practices, e.g. ORC properties, that help reduce this overhead?

user_6147064 · Answer

Well this is fairly simple. The very first time your query would pick a random record from your data. On top of that it is not guaranteed that you have read the very first record, since ORC files are splittable and can be processed in parallel. On the other hand, the second query looks for a specific record.

Here is an analogy. Let's assume you have 100 coins UUID and some other info imprinted at on their backs. All of them are face up on a table, so you can't see their UUID.

select * from my_table limit 1

This query is like you flipped some random coin, looked at what it is written on the back and put it back on a table face up. Next, someone came and shuffled all of the coins.

select * from my_table where uuid= limit 1

This query is like you wanting to look at the information written on the back of a specific coin. It is unlikely that you would flip the correct coin with your first try. So you would need to "scan" more coins (data).

One of the common ways to reduce size of scanned data is to partition your data, i.e. put it into separate "folders" (not files) in your S3 bucket. Then "folder" names can be use as a virtual columns within your table definition, i.e. additional metadata for your table. Have a look at this post, which goes into mor details on how to optimise queries in Athena.

Understanding data scanned when querying ORC with Presto/Athena

1 Answers