Free tools Windows power users keep installed
One-click scans. No signup required.
A PostgreSQL index is an option, not an instruction. The planner estimates the cost of available plans and may choose a sequential scan when it expects that reading the table directly will be cheaper than finding matching rows through an index. To understand an unexpected choice, inspect the plan and its row estimates before changing the index or trying to force a scan type.
This guide follows the PostgreSQL 18 documentation, accessed October 5, 2026. Details can change across PostgreSQL versions.
As an Amazon Associate I earn from qualifying purchases.
What an index does—and what it does not promise
An index gives PostgreSQL another route to find rows. It does not guarantee that every query with a matching condition will use that route. The planner compares alternatives using estimated costs, including how many rows are likely to match and how much table data each plan may need to read.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →For a selective condition, an index can reduce the number of table pages PostgreSQL must visit. But an index scan may find matching entries scattered across the table and then require many separate heap fetches. When many rows qualify, those fetches can cost more than reading the table sequentially. A sequential scan in that situation can be a sound choice, not evidence that PostgreSQL failed to notice the index.
#1 Best Overall
Which index method fits the query?
B-tree is PostgreSQL’s default index method. It suits equality and range comparisons on ordered values, including predicates such as BETWEEN and IN, and it can support sorted retrieval. Other index methods serve different indexable clauses; they are not interchangeable alternatives for every predicate.
| Method | What PostgreSQL 18 documentation establishes | Practical implication |
|---|---|---|
| B-tree | Supports equality and range comparisons on ordered values and can provide sorted retrieval. | Consider it for those comparisons and when the query needs ordered output. |
| Hash | Available as a distinct index method for different indexable clauses. | Check whether its supported clause matches the query; do not assume it substitutes for B-tree. |
| GiST | Available as a distinct index method for different indexable clauses. | Match the method to the query’s operators and data shape. |
| SP-GiST | Available as a distinct index method for different indexable clauses. | Match the method to the query’s operators and data shape. |
| GIN | Available as a distinct index method for different indexable clauses. | Match the method to the query’s operators and data shape. |
| BRIN | Available as a distinct index method for different indexable clauses. | Match the method to the query’s operators and data shape. |
For any method, verify that the index supports the operators in the actual predicate. Index choice also has workload trade-offs: compare the query pattern with write and update activity, index size, and any need for ordering. The method names alone do not establish that one is faster for a particular workload.
How a B-tree is organized
A PostgreSQL B-tree is a multi-way balanced tree built from pages, not a binary tree. Pages form levels: searches move through the tree toward the relevant leaf page, while pages at each level are linked as doubly linked lists. This organization supports both lookup and ordered traversal.
Rank #2
When an item cannot fit on a page, PostgreSQL can split the page. It moves some items to a new page and adds a downlink to that page in the parent. If the parent cannot fit the new downlink, that page can split too. A split can therefore cascade up the tree; if the root splits, PostgreSQL creates a new top level.
A split is a structural operation that makes room. By itself, it is not proof that the index is corrupt or unusable. PostgreSQL’s B-tree implementation attempts tuple cleanup in some circumstances before splitting, but that behavior does not guarantee that a split will be avoided.
What B-tree fillfactor changes
Fillfactor controls how full B-tree leaf pages are made during an initial build and when the index is extended at the right with new largest keys. Less-full pages leave room for later inserts, but whether that helps depends on the workload. PostgreSQL documents a default of 90 and says values from 50 to 90 may smooth early page splits for some insert or update workloads—not that they will improve every index.
| Setting | Documented meaning | When to consider it |
|---|---|---|
| 90 (default) | PostgreSQL’s documented default B-tree fillfactor. | Use as the baseline unless measurements or a known workload justify testing a change. |
| 50–90 | A documented range that may smooth early page splits in some insert/update workloads; the effect is workload-dependent. | Benchmark against the actual write pattern, index size, and read performance rather than selecting a universal value. |
Fillfactor is a tuning lever, not a fix for an unsuitable index method, a mismatched predicate, or inaccurate row estimates.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteHow to investigate an index that appears to be ignored
-
Run
EXPLAINon the exact query. For example:EXPLAIN SELECT * FROM orders WHERE customer_id = 42;Read the scan node, estimated rows, index conditions if present, and total estimated cost. The plan shows the path PostgreSQL estimates is cheapest for that query. -
Check whether the predicate fits the index. Confirm the index method supports the predicate’s operators and data type, and consider whether the condition is selective enough for an index route to reduce the work.
-
Compare estimates with execution when it is safe to run the query.
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;executes the statement and reports actual rows and timings alongside estimates. Use extra care with data-changing statements:EXPLAIN ANALYZEexecutes them too. -
If the data has changed substantially or statistics may be stale, run
ANALYZE orders;and inspect the plan again.ANALYZEcollects statistics about value distributions; PostgreSQL uses those statistics to estimate how many rows a condition will return. Stale or unrepresentative estimates are one possible reason for a surprising plan.Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Weigh the likely table-page access, not just the existence of an index. If many rows qualify or the index route would lead to many scattered heap fetches, a sequential scan may still be less costly.
PostgreSQL’s documented costs are arbitrary units, not elapsed-time promises. Sample plans in the manual illustrate plan choices; their row counts and timings are not universal benchmarks, and estimates can vary with the platform and data.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Why forcing an index scan is not the default fix
Forcing index use can be useful as a controlled diagnostic: it tests what happens under an alternative plan. It does not show that the forced plan is better for production. Compare the plans and, where safe, actual execution on representative data before changing production behavior. PostgreSQL’s documentation emphasizes that index decisions depend on the workload and often require experimentation.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
