[ckuehne]

"What’s going on here? Why is Postgres wasting its time, continuing to search even though it’s already found the data we’re looking for?"

Because Postgres has not already found what we are looking for. The query is

  select *
  from users
  where name = 'Capitain Nemo'
  order by id asc
  limit 1;

Only after Postgres has found all users with the name 'Capitain Nemo' it can sort them by their 'id' attribute and limit the result set to the first one in the sorted relation.

Otherwise very nice post though.

[ghayes]

Yes, the OP here [0] says that the query was one that was generated from ruby's ActiveRecord. Clearly, removing the sort clause would speed up this query. I believe the issue (from the ruby perspective) is using ActiveRecord's `User.where(name: 'Captain Nemo').first` instead of `User.where(name: 'Captain Nemo').take` [1]

[0] https://news.ycombinator.com/item?id=8449329 [1] http://api.rubyonrails.org/classes/ActiveRecord/FinderMethod...

[Animats]

The author doesn't show the schema for the table. If the "Name" field was UNIQUE, the search could stop at the first hit. Most SQL optimizers are smart enough to do this. UNIQUE is usually used with an index (otherwise, inserting a new record requires a full table scan for the uniqueness check) but that's not required.