When adding an index makes things slower

The table of daily figures has one key: the counter and the date together. Five places in the code ask it a different question — not "this counter over time" but "all counters on these dates". The homepage gallery, the toplist, the sitemap, a 90-day chart, and the status figures. Without an index on the date column, every one of those reads all 114,113 rows.

Adding that index makes the gallery ten times faster. It was not added. Here is the measurement that decided it.

One query got much faster

The gallery asks for the counters that were active today. With no index on the date it scans the whole table; with one it looks up a single date and reads 443 rows. 61.5 milliseconds became 6.1. That is not a subtle result, and if it had been the only thing measured, the index would be in the database now.

Two queries got slower

The sitemap asks for fourteen days and got 19% slower. The status figures ask for a year and got 10% slower. In both cases the query planner switched from the primary key to the new index and made a worse choice.

This is the part worth understanding, because it is not a bug in the planner. Reading a range through a secondary index means finding the matching rows in the index and then fetching each one from the table. For a narrow slice that is a bargain. For fourteen days out of thirteen months it is still a lot of rows fetched one at a time, and a straight scan of the table — reading it in physical order, which is what disks and caches like — beats it. The planner does not know that. It sees a range and an index and takes it.

You do not get to say "use this index for the gallery only." An index is available to every query that touches the column, and the planner will use it wherever its estimates say it should. Adding one is a change to every query on that table, not to the one you had in mind.

Together: nothing

Across all five, 555 milliseconds became 524. A 1.1× improvement, which on a Raspberry Pi that serves these pages from a cache anyway is not worth a change to the schema.

The 90-day chart looks like it improved by 9%. It is not counted, and it is worth saying why. Every query was measured three times over: without the index, with it, then without it again. If the second run without the index does not land near the first, the difference was the machine, not the change. For the 90-day chart the two runs without the index differed by 13.8% — more than the effect being claimed. So that row measures nothing, and it is reported as nothing.

How this was measured, and why that matters

Not on the live table. The script copies it, works on the copy, and drops the copy at the end. Adding an index to a production table to see what happens is a thing you can only do once per surprise.

The queries are the real ones, lifted from the code, including the join onto the counter table. An earlier version of this test used a simplified stand-in and produced a much more exciting answer: twenty times faster. The simplified query was not one that anything actually runs.

What the number would have been

Had this been measured the ordinary way — take the query that feels slow, time it before and after — the answer would have been "ten times faster, ship it". The index would have gone in, the gallery would have got quicker, the sitemap and the status page would have got slower, and nobody would have connected the two, because nobody was watching those.

That is the general shape of it. A change to shared infrastructure cannot be evaluated on the case that motivated it. The question is not "does this help the thing I am looking at" but "what else touches this, and what happens to that".

There is a version that might work: an index carrying both columns, date first, so the gallery can be answered from the index alone without going back to the table. It may also avoid tempting the planner on the wider ranges. That has not been measured, so it is not a recommendation — it is the next thing to put on the copy.