A 40ms Postgres query that took 11 minutes on our French VPS

Last spring we moved a small internal tool (Rails 7.0, Postgres 14.9, Ruby 3.2.2) onto a single VPS in Roubaix, 4 vCPU / 8 GB, Debian 12. The database was about 6 GB, dominated by one table: events, roughly 22 million rows, mostly append-only. A dashboard query that ran in 40ms on my laptop took 11 minutes in production. Same schema, same data. That gap is the whole story.

Here is the query, reduced:

SELECT user_id, max(created_at) AS last_seen
FROM events
WHERE event_type = 'login'
GROUP BY user_id
ORDER BY last_seen DESC
LIMIT 50;

On the laptop it used an index on (event_type, user_id, created_at). On the VPS it did a sequential scan over 22M rows, sorted them, then sorted again. The index existed. The planner just would not use it.

First thing I checked was the planner’s cost model, not the query:

SHOW random_page_cost;
SHOW effective_cache_size;
SHOW default_statistics_target;

random_page_cost was 4 (the default), and effective_cache_size was 4GB out of 8GB RAM with shared_buffers at 128MB because nobody had touched the config after install. On a VPS with a network-attached volume, random reads are not 4x sequential; they are much closer. I set random_page_cost = 1.1, bumped shared_buffers to 2GB, effective_cache_size to 5GB, and ran ANALYZE events. The planner switched to an index-only scan and the query went to about 90ms. Not 40ms, but the same order of magnitude.

Then the second half: the dashboard was calling it once per page load, and three users had the page open in a loop. 90ms x 3 x every 30s is fine, but the first version had 11 minutes of blocking. I added a materialized view refreshed every five minutes, because nobody needs login timestamps to the second. That was the actual fix; the planner tuning just made the view build cheap enough to run on schedule.

What I would flag: this only works because events is append-only. If rows get updated in place, the index-only scan loses its visibility map benefit and you are back to heap fetches. I hit that when I added a soft-delete column. Also, random_page_cost = 1.1 is wrong for spinning disks and probably wrong for some cloud volumes; measure your own storage before copying the number. And if your VPS provider oversubscribes CPU, the planner might pick a parallel plan that makes things worse under contention; I set max_parallel_workers_per_gather = 0 on this box after one bad afternoon.

The thing I still do not understand: why ANALYZE alone did not fix it. The stats looked reasonable. If anyone has a story where the planner was right to ignore a perfectly good index, I would like to hear the mechanism, because mine still feels like luck.