How to convince PostgreSQL to use an index?

Question

PostgreSQL

There is a table like

label | timestamp
 AAA  | 1408937820000
 AAA  | 1408937880000
 AAA  | 1408937940000
 AAA  | 1408938000000
 BBB  | 1408938120000
 BBB  | 1408938180000
 BBB  | 1408938240000
 BBB  | 1408938300000
 BBB  | 1408938360000

That is, some labels and timestamps for them. There is a combined index for all this (label, timestamp). There is also a set of columns with numbers, but they are used as ballast and do not participate in the selection.
In this table, from time to time, you need to look for the latest available timestamps for all labels.

SELECT
  label, max(timestamp) as latest
FROM
  table
GROUP BY label;

The problem is, this query ↑ does not use an index and takes almost 2.5 hours (≈ 70 mega rows). In this case, a request for a specific label uses an index and is completed in seconds.

SELECT
  label, max(timestamp) as latest
FROM
  table
WHERE label = 'AAA';

It gets ridiculous - if you take a list of unique tags (it is ready in a separate table) and make 100500 separate queries, it turns out an order of magnitude faster, everything fits in ≈ 10 minutes.
From what I already managed to google, it turns out that you can’t force PostgreSQL to use an index, and this is not a bug, but a feature. The question is, are there any magical techniques that would allow me to get the data I need quickly and in one request? Or at least two or three. As I said before, I have a table that already has a list of unique labels. This can help?

Answer 1

I have a table that already has a list of unique labels

try this:

select label, latest_stamp from labelstable lateral (
    select timestamp as latest_stamp from tablename where tablename.label = labeltable.label
    order by timestamp desc limit 1
) l

PostgreSQL 9.3 or higher.
Loose index scan on the table itself can also be done, it will just be more verbose.