3 ms·

I’ll genuinely never understand why people benchmark in this way. They are doing a thing that is slow and inefficient in a fundamental way and then producing a

I’ll genuinely never understand why people benchmark in this way. They are doing a thing that is slow and inefficient in a fundamental way and then producing a blogpost about it being slow and inefficient in a much more trivial respect. If you care about performance, select count(uuid) from records; Makes absolutely no sense. You know the uuid is unique so you are deliberately selecting a value and then throwing it away just to count it. select count(1) from records; Would give the same value and could be answered from the index without requiring a scan of the table. This is true of any unique column in the table no matter what type (it doesn’t have to be a uuid). The fact that uuids are a bit slower than other types of keys when you scan the entire table unnecessarily seems beside the point.

ants_a 3y ago
I agree, though it's not the uniqueness, but not null constraint that matters here. PostgreSQL could, but doesn't right now, use the not null constraint to optimize the former to the latter. The actual case came to my attention because an ORM insisted on generating the count(uuid) variant and I thought it peculiar that the performance difference was so large. But silly ORMs aside, the same problem will happen on any index only range scan over an index that has uuid in it. For a more realistic case, a `count(*) where somecol = 'value'` with a `(somecol, uuid)` index will hit the same problem. I thought the rather hidden single entry visibility map buffer reference cache was an interesting example where random ordering can cause performance problems.
insanitybit 3y ago
I can't imagine that the author is saying "don't count uuids this way!" but instead is trying to explain how locality works in btree indices, using a trivial example to demonstrate it.
seanhunter 3y ago
I should add for people who are not aware, the same concept is very important to bear in mind if there is a WHERE EXISTS or WHERE NOT EXISTS subclause to your query. Something like select u.username as good_user from users u where not exists ( select 1 from naughty_users n where n.id = u.id ) …is generally going to be much much faster than what a lot of people instinctively do which is to select the username or user id from the inner query. Since you’re just checking for (non)existence it doesn’t matter what the inner query returns so select 1 will answer the query from the index (assuming the id is indexed which if it isn’t you have bigger problems). If you spend a while looking at explain plans for your queries you will spot common patterns like this where you can avoid a table scan often.

ants_a 3y ago

I agree, though it's not the uniqueness, but not null constraint that matters here. PostgreSQL could, but doesn't right now, use the not null constraint to optimize the former to the latter. The actual case came to my attention because an ORM insisted on generating the count(uuid) variant and I thought it peculiar that the performance difference was so large. But silly ORMs aside, the same problem will happen on any index only range scan over an index that has uuid in it. For a more realistic case, a `count(*) where somecol = 'value'` with a `(somecol, uuid)` index will hit the same problem. I thought the rather hidden single entry visibility map buffer reference cache was an interesting example where random ordering can cause performance problems.

insanitybit 3y ago

I can't imagine that the author is saying "don't count uuids this way!" but instead is trying to explain how locality works in btree indices, using a trivial example to demonstrate it.

seanhunter 3y ago

I should add for people who are not aware, the same concept is very important to bear in mind if there is a WHERE EXISTS or WHERE NOT EXISTS subclause to your query. Something like select u.username as good_user from users u where not exists ( select 1 from naughty_users n where n.id = u.id ) …is generally going to be much much faster than what a lot of people instinctively do which is to select the username or user id from the inner query. Since you’re just checking for (non)existence it doesn’t matter what the inner query returns so select 1 will answer the query from the index (assuming the id is indexed which if it isn’t you have bigger problems). If you spend a while looking at explain plans for your queries you will spot common patterns like this where you can avoid a table scan often.