Hacker News Reader

I was under the impression that unlike MySQL, Postgres can use two indecies at once when doing a query. So in MySQL you could do a query like `SELECT * FROM tbl WHERE a = 1 ORDER BY b` and you'd want an index on (a, b). But in Postgres you could have a separate index on a and b and you'd get the performance gain. In fact I thought in Postgres land you'd be better off creating them as separate indecies.

Is this info now outdated?

View on HN

Not's not just outdated, its wrong (in context of question 2) which is about indexing both, `where` and `order by` clauses. In that case you must provide a single index to benefit from the index order (so that the database doesn't actually need to perform a sort operation).

You get some speedup from both indexes, but not the same.

On a related situation, having `WHERE a = 1 AND b = 2` indexes on `a` and `b` just can not have the same performance as an index on `(a, b)`, because you will inevitably have to scan one of the indexes looking for matches. On the case you posted, of an order by, I don't think you get any speedup on the index on `b` at all.

Besides, that `LIMIT 1` at the end of the clause is important to the question.

Reply on news.ycombinator.com