Testing Index Assumptions Across PostgreSQL, MySQL, SQL Server, and Oracle

I built this experiment because I wanted to check something that comes up often in backend work: an index can look right in a code review, but the database may still choose a different path.

That is easy to forget when we discuss indexing in abstract terms. We say things like "add an index on the filter column" or "put the sort column after the equality columns." Those are useful starting points, but they are not the final answer. The optimizer still has to decide whether the index is worth using for the actual data distribution, query shape, joins, and row counts.

So I set up the same digital wallet and payments schema on PostgreSQL 16, MySQL 8, SQL Server 2022, and Oracle Database Free 23c. I kept the business problem consistent across all four engines and tested the same query scenarios before and after adding indexes. My goal was not to prove that one optimizer was smarter than another. I wanted to see where my indexing assumptions held up and where the plans pushed back.

This was not meant to rank the databases. The lab runs locally in Docker, the data is generated, and the random values are not identical across engines. I treated the timings as supporting evidence, not as a benchmark leaderboard.

The questions I cared about were more practical:

• Did the access path change from scan to seek or index scan?

• Did the join strategy change?

• Did an index remove or reduce a sort?

• Did the optimizer ignore an index that looked reasonable?

• Was an existing unique or foreign-key index already enough?

The last point ended up being more important than I expected, especially because it is the kind of detail that is easy to miss during a rushed production investigation.

Building The Experiment

I used a fintech-style schema because the query patterns are familiar enough to reason about without inventing a strange workload: users, wallets, beneficiaries, merchants, payments, transactions, ledger entries, and audit logs.

Figure 1: Simplified fintech schema used across all four database engines.

The sample data is intentionally modest:

For each scenario, I followed the same process:

• Run the baseline query.

• Capture the execution plan.

• Add the index that should help.

• Refresh optimizer statistics.

• Run the same query again.

• Compare the access path, join strategy, sorting, and row estimates.

The query scenarios were:

Figure 2: Query scenarios mapped to the tables and index patterns tested in the lab.

These are the kinds of queries that show up in real systems: point lookups, activity feeds, reporting queries, date-range aggregation, reconciliation checks, and audit searches. Once the setup was in place, I started with the query where I expected the index to behave most predictably.

When The Activity Feed Index Worked As Expected

The transaction history query fetches the latest transactions for one user:

SELECT
    t.id,
    t.transaction_type,
    t.amount,
    t.currency,
    t.status,
    t.reference_number,
    t.created_at
FROM transactions t
JOIN wallets w ON w.id = t.wallet_id
JOIN users u ON u.id = w.user_id
WHERE u.email = 'user1000@example.com'
ORDER BY t.created_at DESC
LIMIT 20;

The index I tested was:

CREATE INDEX idx_transactions_wallet_created_at
ON transactions(wallet_id, created_at DESC);

I expected this one to work. The query narrows down to a wallet and then asks for recent rows, so (wallet_id, created_at DESC) lines up with both the filter and the ordering.

PostgreSQL gave the cleanest before-and-after result. Before the index, the plan scanned transactions, joined through hash joins, and sorted by created_at DESC. After the index, PostgreSQL used an index scan and nested loops.

In the screenshot below, execution time dropped from about 79 ms to about 3 ms. The before-index plan is first, followed by the plan after adding (wallet_id, created_at DESC).

Figure 3: PostgreSQL transaction history query before and after adding the composite wallet/date index.

SQL Server showed the same kind of improvement in plan shape. It moved from a clustered index scan on transactions to an index seek. The estimated rows read dropped from 100,000 to about 10.

Oracle also moved from full table scans and hash joins to an index range scan and nested loops. Its plan cost dropped from 264 to 37.

MySQL was the useful counterexample. It was already using foreign-key indexes before I added the composite index:

• fk_wallets_user

• fk_transactions_wallet

The new index still helped, but the timing moved only from about 2.26 ms to 1.90 ms.

That changed how I looked at the result. The composite index was valid, but MySQL already had a reasonable route through the data. If I had only looked at the new index definition, I would have overstated the improvement.

This matters in production because adding another index is not free. It affects writes, storage, maintenance, and future optimizer choices. If an existing foreign-key index already gets the query close enough, the right decision may be to leave the schema alone and focus somewhere else.

The next query was less clean, which made it more useful.

The Reporting Index That Looked Better On Paper

The merchant payment report tests successful payment totals by merchant. The index looked reasonable:

CREATE INDEX idx_payments_status_merchant
ON payments(status, merchant_id);

I initially assumed that filtering by status and grouping by merchant_id would make this index attractive.

That assumption did not hold across the engines.

In this sample data, status = 'SUCCESS' matches a large portion of the payments table. PostgreSQL, SQL Server, and Oracle did not treat the index as the obvious access path. Scanning a large part of the table was still competitive or cheaper.

MySQL did use the index differently: it changed the join order, scanned the smaller merchants table first, and then used indexed lookups into payments.

My read on this result is that the optimizers were reacting to the low selectivity of status. If most rows are successful payments, the index does not eliminate much work. In a larger dataset, I would want to test different status distributions before deciding whether this index belongs in the schema.

This is the kind of result I like to see in a performance lab because it exposes a common mistake. An index can match the query text and still be a weak choice if the leading predicate is not selective enough.

The execution plan is where that becomes visible. The index definition alone does not prove the index is useful. After that, I wanted to see whether a simpler date-range index would behave more consistently.

Date-Range Aggregation Did Not Produce A Single Pattern

For daily transaction volume, I tested a simple date index:

CREATE INDEX idx_transactions_created_at
ON transactions(created_at);

PostgreSQL used it and changed from a sequential scan to a bitmap index scan. The recorded execution time dropped from about 18 ms to about 3 ms.

MySQL also used the index and changed from a table scan to an index range scan. The recorded execution time dropped from about 35 ms to about 12 ms.

SQL Server and Oracle did not show the same kind of win in the recorded results. Their plans stayed closer to scan, aggregate, and sort patterns.

While testing this query, the main reminder was that a date predicate does not automatically make a plain date index the dominant path. The amount of data touched, the aggregation work, the predicate shape, and the optimizer's cost model all matter.

I would not turn this into a broad claim that SQL Server or Oracle do not benefit from date indexes. That would be the wrong conclusion. The narrower and more accurate conclusion is that, for this data shape and this query, those optimizers did not choose the new index as the main access path.

That distinction matters when you are writing up performance results for a team. It is easy to make the result sound more universal than it is. For a production incident, this is also the kind of query where I would check actual date-range selectivity, histogram quality, and whether the aggregation itself is the real cost.

The next scenario had fewer surprises because the index matched the filter and sort pattern much more tightly.

The Audit Log Index Matched The Query Shape

Audit log search was the most straightforward composite-index case.

The query pattern is simple: filter by entity, filter by action, and return the most recent events. The index was:

CREATE INDEX idx_audit_logs_entity_action_created_at
ON audit_logs(entity_name, action, created_at DESC);

This matched the query shape cleanly: equality filters first, then the order-by column.

All four engines showed useful plan changes:

• PostgreSQL moved from a sequential scan plus top-N sort to an ordered index scan.

• MySQL moved from a table scan plus sort to a composite index lookup.

• SQL Server moved from a clustered index scan plus sort to an index seek with clustered key lookups.

• Oracle moved from full table access and sort to an index range scan.

SQL Server also showed a concrete I/O improvement here. Logical reads dropped from 1070 to 314.

Figure 4: SQL Server audit log query before and after adding the composite entity/action/date index.

Looking back, this was the most predictable index in the experiment. The filter columns were specific enough, the ordering requirement matched the trailing column, and each optimizer had a clear reason to use the index.

This is the kind of plan change I would feel more comfortable defending in a review. It does not only make the query faster in one run; it gives the database a narrower access path and removes work that was visible in the original plan.

Observations After Reviewing The Plans

The most interesting part was not that indexes helped. It was that the reason changed by engine.

PostgreSQL made some changes easy to read with EXPLAIN ANALYZE: scan to index scan, hash join to nested loop, sort reduced or avoided.

MySQL reminded me to inspect existing indexes before adding more. In the transaction history query, foreign-key indexes were already doing useful work.

SQL Server made row-estimate changes especially visible. In the transaction history query, the estimated rows read falling from 100,000 to about 10 said more than the index name alone.

Oracle was the clearest reminder that full scans are not automatically wrong. When a query touched a large portion of a table, Oracle often stayed with full table access and hash aggregation instead of forcing the new index into the plan.

None of these observations are universal rules. They are observations from this schema, data volume, Docker setup, and query set.

Assumptions That Turned Out To Be Wrong

• A matching index definition does not guarantee index usage.

• A full table scan is not automatically a bad plan.

• Existing foreign-key or unique indexes may already solve most of the access problem.

• The same query shape can lead to different join strategies across engines.

• A faster timing is less convincing if the plan shape did not actually change.

What Changed In My Performance Investigation Process

After this experiment, I would be more careful about how I approach index reviews.

First, I would check the existing access paths before proposing a new index. Unique constraints, foreign-key indexes, and existing composite indexes may already be doing most of the work.

Second, I would look at selectivity before getting attached to an index. The (status, merchant_id) case is a good example. The index matched the query, but status = 'SUCCESS' touched enough rows that several optimizers had little reason to prefer it.

Third, I would compare plan shape before comparing timing. Timing is useful, but the plan tells me whether the database actually changed strategy or whether I am just seeing noise from a local run.

Fourth, I would refresh statistics as part of the test, not as an afterthought. Without current statistics, the optimizer may be making a decision based on stale assumptions.

Finally, I would write conclusions narrowly. "This index helped this query on this data shape" is more useful than pretending one local test produced a general rule for every workload.

Questions I Would Ask Before Adding The Next Index

After reviewing the plans, I would not start with "what index can I add?"

I would start with these questions:

• Is there already a unique, foreign-key, or composite index being used?

• Is the leading column selective enough?

• Does the index match the filter, join, and order-by pattern?

• Is the query touching so much data that a scan is reasonable?

• Did I refresh statistics after changing indexes?

• Did the plan actually change, or did only the schema change?

That last question is easy to overlook. Creating an index feels like progress. The execution plan decides whether it was progress.

Running The Lab Locally

The lab runs with Docker Compose:

docker compose up -d

The repository includes engine-specific schema scripts, seed scripts, index scripts, performance queries, result notes, and Mermaid diagrams.

For a clean rerun after changing schema or seed data:

docker compose down -v
docker compose up -d

Then run the baseline query, capture the plan, apply the index, refresh statistics, and run the query again.

What Changed My Thinking About Indexing

The most useful part of this lab was not the fastest number in a result table. It was seeing where my expectation did not match the optimizer's choice.

That is the practical takeaway I would carry into an index review: do not stop at "the index matches the query." Check whether the plan uses it, check what work disappeared, and check whether the improvement is meaningful enough to justify another index in the system.

An index is only helping if the execution plan says it is helping.

GitHub Repository

https://github.com/Rumman90/database-performance-lab