Postgres

One index took a lookup from 84 ms to 0.04 ms

stummel looked up every code with a sequential scan over 1.2 million clicks’ worth of links. EXPLAIN ANALYZE showed it in one line.

0:00
/0:06
Before and after the index, in psql.

Every redirect looked up its code with a sequential scan. At 40,000 links that took 84 ms, longer than the rest of the request together. One unique index brought it to 0.04 ms.

psql output: an index scan on links_code_key with an execution time of 0.042 ms.
The plan after the index.
CREATE UNIQUE INDEX CONCURRENTLY links_code_key ON links (code);

CONCURRENTLY builds it without locking the table. It took 3 seconds and nobody noticed, which is the point.

Comments

Buy Logbuch