Query optimization as a cost lever: the cheapest capacity you can buy

Quick answer

Query optimization is the highest-return database cost lever available because it is a one-time engineering cost against a recurring infrastructure saving. A single missing index that turns a 400 millisecond sequential scan into a 2 millisecond index lookup removes roughly 99.5 percent of that query's resource consumption. If that query was 30 percent of database CPU, fixing it frees 30 percent of capacity, which is often a full instance size: db.r6g.2xlarge to db.r6g.xlarge saves $378 per month, $4,536 a year, forever, for a few hours of work. Find candidates with pg_stat_statements ordered by total execution time, not by mean time, because a fast query run ten million times costs more than a slow one run twice.

Most database cost work is about buying less of something. Query optimization is different: it makes the same hardware do more, which means it is the only lever that improves cost and performance at the same time. It is also the one most often skipped, because it requires engineering attention rather than a console change.

Why the return is so high

Infrastructure savings are recurring; optimization work is one-off. An afternoon spent adding an index that frees 30 percent of database CPU, enabling a one-size downsize from db.r6g.2xlarge ($756) to db.r6g.xlarge ($378), returns $4,536 in the first year and the same every year after. Expressed as a rate of return on four hours of engineering time at roughly $96 per hour, that is a payback period of about two and a half days.

Intervention	One-off cost	Annual saving	Payback
Add missing index on hot query	about $400	$4,536	about 2.5 days
Rewrite N+1 query pattern	about $1,500	$2,268	about 8 months
Add application cache for hot read	about $2,500	$2,268 minus $600 cache	about 18 months
Buy a larger instance	$0	minus $4,536	never

Finding the queries that matter

Enable pg_stat_statements (or Performance Schema on MySQL) and sort by total_exec_time, not mean_exec_time. This is the single most important instruction here. A query averaging 800 milliseconds run 50 times a day consumes 40 seconds of database time daily. A query averaging 4 milliseconds run 8 million times a day consumes 8.9 hours. The second is thirty times more expensive and will never appear in a slow query log with a 500 millisecond threshold.

Pull the top twenty by total time and compute each one's share of total database time. In most systems the top three queries account for 40 to 70 percent of all database work, which means three fixes reach most of the available saving. Then for each, run EXPLAIN (ANALYZE, BUFFERS) and look for the usual suspects: sequential scans on large tables, nested loops over big row counts, sorts spilling to disk, and hash joins with high memory usage.

The four fixes that account for most of it

Problem	Typical fix	Typical load reduction
Sequential scan on a filtered column	B-tree index on the predicate	90 to 99 percent
N+1 query from an ORM	Eager loading or a single join	95 percent of round trips
SELECT * on wide tables	Project only needed columns	40 to 80 percent of I/O
Sort spilling to disk	Raise work_mem or add a covering index	50 to 90 percent of that query

The SELECT * case deserves attention because it is so common and so easy to fix. A table with a large JSONB or text column that the application never reads on list views forces PostgreSQL to fetch and detoast that data for every row. Projecting the six columns actually rendered can cut the query's I/O by an order of magnitude with a one-line change to the query.

The work_mem case is a genuine cost trade. Raising work_mem from the default 4 MB to 64 MB stops sorts spilling to disk, but work_mem is allocated per sort or hash node per connection, so 200 connections each running a query with three sort nodes could in principle allocate 38 GB. Set work_mem per role or per session for the reporting queries that need it rather than globally, which gets the benefit without sizing the instance around a worst case.

Indexes are not free

Every index adds write amplification and storage. A B-tree index on a column of a 200 million row table typically occupies 4 to 8 GB, about $0.46 to $0.92 per month at gp3 rates, which is trivial. The write cost is less trivial: each INSERT and each UPDATE touching the indexed column must maintain the index, and a table with fifteen indexes can spend more time on index maintenance than on the row write itself.

So audit for unused indexes while you are adding useful ones. Query pg_stat_user_indexes for idx_scan = 0 over a representative period. A typical mature database carries three to eight indexes that no query has used in months, each consuming storage and slowing every write. Dropping them is a saving with no downside, provided you have confirmed the sample period covers monthly and quarterly jobs.

Making it a habit

Put a recurring item in the sprint: pull the top ten queries by total time, check whether the ranking changed, and fix anything new that reached the top three. A team doing this quarterly typically avoids one instance upgrade a year per significant database, which on a fleet of ten databases averaging $500 per month is $30,000 of avoided annual growth.

And when an optimization frees capacity, actually capture it. Freed CPU that nobody downsizes against is a performance improvement, not a saving. Price the smaller shape from Terraform, schedule the change, and book the difference. Compare instance options against the resource catalog, and see indexing cost and performance for the index-side detail.

FAQ

Why is query optimization a good cost lever?

Because it is a one-off engineering cost against a recurring infrastructure saving. Four hours spent adding an index that frees 30 percent of database CPU can enable a downsize from db.r6g.2xlarge at $756 per month to db.r6g.xlarge at $378, returning $4,536 a year, every year. At a loaded engineering rate of about $96 per hour, the payback period is roughly two and a half days.

Should I sort slow queries by mean time or total time?

Total time, always. A query averaging 800 milliseconds run 50 times a day consumes 40 seconds of database time daily; a query averaging 4 milliseconds run 8 million times consumes 8.9 hours. The second is thirty times more expensive and never appears in a slow query log with a 500 millisecond threshold. Use pg_stat_statements ordered by total_exec_time.

Which query fixes give the biggest load reduction?

Adding a B-tree index on a filtered column typically removes 90 to 99 percent of that query's cost. Eliminating an ORM N+1 pattern removes about 95 percent of round trips. Projecting only needed columns instead of SELECT * cuts 40 to 80 percent of I/O on wide tables with large text or JSONB columns. Fixing a sort that spills to disk removes 50 to 90 percent of that query's cost.

Do indexes cost anything?

Yes, on both storage and writes. A B-tree index on a 200 million row table typically occupies 4 to 8 GB, about $0.46 to $0.92 per month at gp3 rates, which is trivial. The write cost matters more: every INSERT and every UPDATE touching an indexed column maintains the index, and a table with fifteen indexes can spend more time on index maintenance than on the row write itself.

Should I raise work_mem to stop sorts spilling to disk?

Selectively. work_mem is allocated per sort or hash node per connection, so raising it globally from 4 MB to 64 MB means 200 connections running queries with three sort nodes could allocate 38 GB in principle. Set it per role or per session for the reporting queries that need it, which captures the benefit without sizing the instance around a theoretical worst case.

How does C3X help turn optimization into savings?

Freed capacity that nobody downsizes against is a performance improvement, not a saving. C3X prices the smaller instance shape from Terraform before you merge the change, so the optimization work produces a concrete figure to book and the downsize gets scheduled rather than forgotten. Seeing the saving in the pull request is what closes the loop between engineering effort and the invoice.

What to do next

Turn freed capacity into a booked saving. C3X reads your Terraform and prices your resources against a live catalog. Start with the quickstart.