I'm experiencing a problem with a GBQ query which goes through all the data in a table, even though the table is partitioned.
The partitioning is set to DAY on the datetime field.
Currently the query I'm trying to run looks like this:
SELECT * FROM `project.dataset.table` t
WHERE datetime = "2023-09-11"
The datetime field is a TIMESTAMP, so I tried it like this: date(datetime) = "2023-09-11" and it still wanted to access all 3GB of data, every time.
I played around with DATE_TRUNC() function, but that was unsuccessful.
Partitioning for small tables won't improve the performance or reduce the cost that's why they are offering clustering instead of partitioning for these kind of tables.
Consider clustering a table instead of partitioning a table in the following circumstances:
Partitioning results in a small amount of data per partition (approximately less than 10 GB)