News for hackers. Stuff that matters.
Re:Following a Select Statement Through Postgres Internals by tofflos on Monday October 13, 2014 @05:52PM (#8450881)
I liked the article, and I know this is nitpicking, but I disagree that creating an index is the best solution for eliminating the sequential scan. Hopefully the next article mentions it.

Just drop sort clause. Returning the user with the lowest id seems like an unusual use case.

Imagine searching for a name in a phone book and only finding the oldest person with that name, or the person with the lowest phone number.

More common cases are checking whether a user exists, which doesn't require a sort, or finding all the users with a particular name, which I guess could be paged using limit and offset.

Re:Following a Select Statement Through Postgres Internals by pat_shaughnessy on Monday October 13, 2014 @07:43PM (#8451245)
Oh absolutely. I thought about saying that, and just ran out of energy before posting the article this morning.

The reason I used this particular SQL statement as an example is that ActiveRecord (a Ruby ORM) generates it from a fairly simple, common Ruby expression. I suppose ActiveRecord could be improved to drop the sort when you know there's one one possible match.

Re:Following a Select Statement Through Postgres Internals by talaketu on Monday October 13, 2014 @09:07PM (#8451429)
Unless "name" is unique, there could be multiple matches. Asking for the "first" demands an ordering.

Perhaps (a) the user doesn't care what ordering is used, and/or (b) (such as in your example in the preceding post) hasn't specified an order (and apparently PK ordering is defaulted to PK - a model option?). It would be error-prone for ORM to take (b) as implying (a).

Re:Following a Select Statement Through Postgres Internals by pat_shaughnessy on Monday October 13, 2014 @09:15PM (#8451455)
Yes, good point. Rails has a lot of conventions like this - that "first" implies ordering by primary key (by default). I just imagine that most Rails developers don't think about sorting and its implications when they ask for the first record.
Re:Following a Select Statement Through Postgres Internals by empthought on Tuesday October 14, 2014 @06:07AM (#8452688)
Ordering by ID can be used in keyset pagination as well as limit/offset: http://use-the-index-luke.com/no-offset
Re:Following a Select Statement Through Postgres Internals by dbenhur on Tuesday October 14, 2014 @01:01AM (#8452033)
Even if you don't care about the id ordering, unless "Captain Nemo" occurs so frequently as to be likely found in the first few pages of the table, the index is a big help.