Queries got slower and the data did not grow
A table was repartitioned by `customer_id` two months ago to speed up per-customer lookups. Those lookups did get faster. Every other query against the table has become progressively slower, and the volume of data has barely changed.
What you would do first
Answer before revealing anything. The value of the exercise is entirely in committing to a diagnosis you can be wrong about.
- 1Count distinct partitions and average file size. A partition count that tracks a business entity rather than a time period is the diagnosis.
- 2Compare a query filtered on
customer_idwith one filtered on date. The first prunes to one directory; the second prunes to none. - 3Look at where the query time is spent — planning and listing versus scanning. If planning dominates, more compute will not help.
- 4Establish the real query mix from the query log rather than from what the team believes it is.
What is actually going on
The trap
The fix that looks right. Read it even if you got the answer — especially then.
Add a second partition column for the date, so that date queries prune too. The partition count is now customers multiplied by days, the files are smaller still, and the listing cost that was the actual problem has been multiplied rather than reduced.