How do you index a table in PostgreSQL so that min/max queries return as quickly as possible?
I have a large table with a couple hundred million rows. Each row as a source_id and a date when the record was last updated. I'd like to collect some statistics for each source_id, specifically the min and max date range for each source_id.
So I created this index on my table:
CREATE INDEX CONCURRENTLY mydata_source_last_updated_date ON mydata (source_id, last_updated_date ASC);
However, when I try to query the min dates per source with:
SELECT source_id, MIN(last_updated_date) FROM mydata GROUP BY source_id;
the query takes about an hour to complete.
Is this normal performance for such a large table, even with an index? How can I reduce this query time?