I need to get the latest entry of a large table matching a certain criteria.
This is my query:
SELECT * FROM "data" WHERE "data"."fk" = 238496 ORDER BY "data"."id" DESC
This query is quite slow. If I do a explain on it, it seems that it uses an
Index Scan Backward.
If I omit the order by on the query:
SELECT * FROM "data" WHERE "data"."fk" = 238496 LIMIT 1
It is very fast. And the explain says that it uses Index scan. This is also
very fast if there aren't any matches. But I've read that I'm not guaranteed
to get the correct match If I do not use a order by, postgres just returns
its fastest possible match. Is this right? But will not the fastest possible
match always be the first match in the index? Is there another way to make
the order by query go faster?