Hacker News Reader

I find the proposed solution for 'SELECT COUNT(customer_id) FROM orders' confusing. They suggest placing the 3 billion order rows into three sets of 1 billion, to be distributed among three machines. But summing the number of distinct customer IDs in each bucket doesn't give you the number of distinct customer IDs in the orders table because an ID could be in more than one bucket. What am I missing?

View on HN

This isn't clear in the blog post; I'll try to clarify.

The buckets correspond to hash token ranges. For example, the database takes one row from orders_2013, hashes the customer_id value, and gets a hashed value of 1.5B. It then puts it into the second bucket orders_2013_[1B-2B[.

After the distributed shuffle, you end up with orders_cust_id[1B-2B[ on machine 2. So all orders that belong to a particular / disjoint set of customers live on one machine. You can then push down the count(distinct customer_id) to each node, and then add up the results.

We use hash token ranges in pg_shard. I'll include this clarification to the blog post.

Indeed, that's confusing. You don't want to push down the count; the plan must execute "select distinct customer_id" separately on each shard, then merge the results and count.

Not sure if that's what they really meant — since their business is sharding, this shouldn't be new to them.

Reply on news.ycombinator.com