Unconventional PostgreSQL Optimizations

Summary

When it comes to database optimization, developers often reach for the same old tools: rewrite the query slightly differently, slap an index on a column, denormalize, analyze, vacuum, cluster, repeat. Conventional techniques are effective, but sometimes being creative can really pay off! In this article, I present unconventional optimization techniques in PostgreSQL. Table of Contents Imagine you have this table of users: db=# CREATE TABLE users ( id INT PRIMARY KEY, username TEXT NOT NULL, plan TEXT NOT NULL, CONSTRAINT plan_check CHECK (plan IN ('free', 'pro')) ); CREATE TABLE For each user you keep their name and which payment plan they're on. There are only two plans, "free" and "pro", so you add a check constraint. Generate some data and analyze the table: db=# INSERT INTO users SELECT n, uuidv4(), (ARRAY['free', 'pro'])[ceil(random()*2)] FROM generate_series(1, 100_000) AS t(n); INSERT 0 100000 db=# ANALYZE users; ANALYZE You now have 100K users in the system. Now you want to let your analysts access this table in their reporting tool of choice. You give one of the analysts permission, and this is the first query they write: db=# SELECT * FROM users WHERE plan = 'Pro'; id │ username │ plan ────┼──────────┼────── (0 rows) The query returned no results, and the analyst is baffled. How come there are no users on the "Pro" plan? The name of the plan is "pro" and not "Pro" (with a capital "P") as the analyst wrote it. This is an honest mistake really, anyone can make such a mistake! But what is the cost of this mistake? Examine the execution plan of a query for a non-existing value: db=# EXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro'; QUERY PLAN ────────────────────────────────────────────────────────────────── Seq Scan on users (cost=0.00..2185.00 rows=1 width=45) (actual time=7.406..7.407 rows=0.00 loops=1) Filter: (plan = 'Pro'::text) Rows Removed by Filter: 100000 Buffers: shared hit=935 Planning: Buffers: shared hit=29 read=2 Planning Time: 4.564 ms Ex...