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.
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.
Is this info now outdated?