Question 1

Why is my PostgreSQL query still slow after adding an index?

Accepted Answer

An index existing and an index being used are different things. The planner chooses a strategy based on its cost estimates, and it will ignore an index whenever it believes a sequential scan will be faster. The first step is therefore not to add another index but to run the query through EXPLAIN ANALYZE, which shows both the plan chosen and the actual time spent at each step.

Several things commonly stop an index from being used.

- Low selectivity. If the query matches a large fraction of the table, reading it sequentially genuinely is cheaper than jumping through an index and then fetching most of the rows anyway. This is the planner being right, not wrong.
- Stale statistics. The planner relies on collected statistics about data distribution. After a bulk load or a large change these can be badly out of date, and refreshing them by analyzing the table often fixes the plan on its own.
- A function or cast applied to the column. Indexing a column does not help a query that filters on a transformation of it, such as a lowercased comparison or a date cast. Either match the expression exactly or create an index on the expression itself.
- Leading column rules. A composite index can only be used from the left. An index on two columns will not serve a query that filters on the second alone.
- Leading wildcards. A pattern match that begins with a wildcard cannot use a standard btree index, because the index is ordered by prefix.

It is also worth confirming the index is the actual bottleneck. If EXPLAIN ANALYZE shows the index scan completing quickly and the time going into a sort, a join, or fetching many rows from the heap, then adding indexes will not help and the query itself needs restructuring.

Question 2

When does a database index hurt performance?

Accepted Answer

Indexes speed up reads by maintaining an ordered structure alongside the table, and that structure has to be kept correct on every write. Every insert, update and delete must update every affected

Question 3

When should you use an array rather than a linked list?

Accepted Answer

Almost always — and the textbook answer, which favours linked lists for insertion and deletion, is misleading on modern hardware because it ignores how memory actually behaves. The theoretical

Question 4

What is a hash table, and why is lookup constant time?

Accepted Answer

A structure that turns a key into an array index by computing a number from it, so finding an item takes roughly the same time whether the table holds ten entries or ten million. The mechanism. A

Question 5

Why is my Docker build so slow?

Accepted Answer

Most slow builds are slow because the cache is being invalidated unnecessarily, not because the work itself is expensive. Each instruction produces a layer, and once one is invalidated every

Question 6

How do you find a performance bottleneck?

Accepted Answer

By measuring before changing anything, because intuition about where time goes is reliably wrong — and the great majority of wasted optimisation effort comes from skipping that step. The order that

Question 7

What is Big O notation actually telling you?

Accepted Answer

Big O describes how an algorithm's cost grows as the input grows. It is a statement about the shape of the growth curve, not about speed in any absolute sense. What it deliberately ignores: Constant

Related Questions