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 minuteShort answer: SQL Server can store a rowstore table as a heap or in one clustered index; InnoDB stores table rows in a clustered index, normally on the primary key; PostgreSQL normally keeps rows in a heap and maintains separate indexes. That physical choice determines how secondary indexes find rows, how wide they become, and which indexing features are available.
The comparison below is about SQL Server rowstore indexes, MySQL with the InnoDB storage engine, and PostgreSQL 18 behavior. Index availability is not a performance guarantee: predicate selectivity, data distribution, selected columns, write volume, and the optimizer’s execution plan decide whether an index helps.
At a glance
| Question | SQL Server (rowstore) | MySQL (InnoDB) | PostgreSQL |
|---|---|---|---|
| Where table rows live | A heap, or one clustered rowstore index that stores rows by clustered key. | A clustered index stores the row data, normally using the primary key. | A heap table with separate indexes accessed through different index methods. |
| How another index reaches the row | A nonclustered index uses a heap row locator, or the clustered key when the table is clustered. | A secondary-index record includes the primary-key columns and uses them to reach clustered row data. | The index accesses the heap; an index-only scan may return all required values without visiting the heap when conditions permit. |
| Subset indexing | Filtered nonclustered indexes. | The InnoDB material cited here does not establish a general equivalent to filtered or partial indexes. | Partial indexes with a predicate. |
| Covering or payload columns | INCLUDE columns on nonclustered indexes; a clustered key is also present in nonunique nonclustered indexes. |
An index is covering when it contains every column the query needs from that table. | INCLUDE columns are payload, not search keys or uniqueness keys. |
| Composite-key behavior | Key order must be validated against the workload; no universal leftmost-prefix rule is established here. | Any leftmost prefix of a multiple-column index can be used for lookup. | B-tree favors constraints on leading columns; GIN and BRIN multicolumn behavior differs. |
SQL Server: the clustered index is a storage decision
A SQL Server rowstore table without a clustered index is a heap. Creating a clustered index changes the table’s row organization: the data rows are stored in clustered-key order, and there can be only one such index. Microsoft Learn summarizes the constraint as: “You can have only one clustered index per table, because the data rows themselves can be stored in only one order.”
How nonclustered indexes find rows
A nonclustered index is a separate structure. On a heap, its leaf entries contain a locator for the heap row. On a clustered table, the clustered key serves as the locator. Consequently, changing the clustered key can affect every nonclustered index because that key is carried into the indexes that need it.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
#1 Best Overall
Included columns and filtered indexes
A nonclustered index can add nonkey columns with INCLUDE. Included columns are stored at the leaf level so a query can sometimes be answered from the index rather than performing a separate lookup. They do not define the index’s search order. Wide or numerous included columns increase storage and the work required for modifications.
A filtered index is a nonclustered index over rows that satisfy a filter predicate. It is useful for a stable, well-defined subset such as non-NULL values or unprocessed workflow rows. Because the predicate has limitations, a filtered index should not be treated as mechanically identical to every PostgreSQL partial-index expression.
MySQL: qualify the comparison as InnoDB
MySQL supports multiple storage engines, so the clustered-row description here applies specifically to InnoDB. Every InnoDB table has a clustered index containing the row data. InnoDB chooses the primary key when one exists. Without a primary key, it uses the first UNIQUE index whose key columns are all NOT NULL; if neither exists, it creates a hidden clustered index named GEN_CLUST_INDEX on an internally assigned row ID.
The primary key is part of every secondary entry
InnoDB secondary-index records contain the secondary key and the primary-key columns used to reach the clustered row. A long primary key therefore makes every secondary index larger. This is a physical-size consequence, not a claim that a shorter key is always the right logical identifier.
Leftmost prefixes and covering indexes
For an index defined as (col1, col2, col3), MySQL documents lookup through the leftmost prefixes: (col1), (col1, col2), or all three columns. An index can also be covering when it contains every column the query uses from that table, allowing the engine to obtain those values from the index tree.
Do not generalize InnoDB’s organization to every MySQL table without checking the deployed storage engine and version.
PostgreSQL: separate heap storage and multiple index methods
PostgreSQL ordinarily stores table rows in a heap and uses separate indexes. PostgreSQL 18 documents B-tree, Hash, GiST, SP-GiST, GIN, and BRIN access methods. These methods are not interchangeable: each supports different operators, data patterns, and maintenance trade-offs.
Partial indexes
A partial index contains only rows satisfying a predicate. This can reduce index size and maintenance when queries repeatedly target a known subset, but the query predicate must be compatible with the index predicate for the planner to use it.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesINCLUDE and index-only scans
PostgreSQL’s INCLUDE columns are nonkey payload. They cannot be used as scan qualifications and do not participate in uniqueness or exclusion enforcement. An index-only scan can return included values without visiting the table when the query and table state allow it. Included values duplicate table data, so wide payload columns can bloat the index.
Multicolumn behavior depends on the method
For PostgreSQL B-tree indexes, searches are most efficient when conditions constrain the leading, leftmost columns. GIN and BRIN multicolumn indexes have documented behavior that is effectively independent of which indexed column is constrained. GiST has its own sensitivity to the first column. Therefore, “put the most selective column first” is not a safe rule for every PostgreSQL index type.
Composite indexes: why column order is not portable advice
The same column list can behave differently across engines and access methods:
- MySQL InnoDB: design around the leftmost prefixes that your lookups actually use.
- PostgreSQL B-tree: place columns according to the leading-column constraints your important queries provide, then validate with plans.
- PostgreSQL GIN or BRIN: do not apply the B-tree leftmost rule; their multicolumn search behavior differs.
- SQL Server: choose key order for the predicates, joins, ordering, and grouping in the real workload, then confirm the optimizer’s choice; the material here does not establish a single cross-workload ordering rule.
Covering indexes are similar in purpose, not identical in implementation
All three systems can sometimes avoid a table lookup when an index contains the values a query needs, but the terminology and mechanics differ.
Best Value
| Engine | What to use | Important limitation |
|---|---|---|
| SQL Server | Key columns plus nonkey columns in INCLUDE. |
Large included payload increases index size and modification cost. |
| MySQL InnoDB | A covering secondary index containing all columns needed from the table. | The primary key is already carried in secondary entries, and a long primary key enlarges them. |
| PostgreSQL | Key columns plus payload columns in INCLUDE. |
Included columns cannot qualify scans or enforce uniqueness; an index-only scan still depends on query and table conditions. |
Filtered and partial indexes
SQL Server and PostgreSQL both support indexing a subset of rows, but the features have different rules. SQL Server calls the feature a filtered index and restricts the filter predicate. PostgreSQL calls it a partial index and permits a predicate expression subject to PostgreSQL’s own matching and planning rules. The InnoDB references used for this comparison describe clustered and secondary indexes, not a general partial-index feature, so do not assume equivalent behavior in MySQL.
Every extra index has a write and storage cost
An index can make selective reads faster while making inserts, updates, and deletes more expensive. Each additional structure consumes space and must be maintained when indexed values or included payload change. A very wide covering index may avoid lookups but cost more than it saves, especially on write-heavy tables.
The optimizer may correctly choose a table or index scan when a predicate matches a large portion of the data. An index being present is not evidence that a particular query will improve.
How to choose an index for a real workload
- Fix the scope: record the engine and version, and for MySQL record the storage engine. For PostgreSQL, record the access method under consideration.
- Start with actual queries: list equality, range, join, sort, grouping, and projection requirements rather than indexing columns in isolation.
- Estimate selectivity and distribution: a predicate matching most rows may not benefit from a narrow lookup index.
- Choose the physical design: decide whether SQL Server should use a heap or clustered rowstore index; choose an InnoDB primary key with secondary-index width in mind; choose a PostgreSQL method that supports the required operators.
- Choose key order and payload deliberately: apply MySQL’s leftmost-prefix behavior, PostgreSQL method-specific rules, and SQL Server workload testing. Add
INCLUDEor covering columns only for demonstrated lookup needs. - Check the execution plan: verify whether the intended index is used, whether estimates are reasonable, and whether lookups, scans, sorting, or residual predicates dominate.
- Measure writes and space: compare insert, update, and delete cost, index size, and maintenance time before keeping the index.
- Recheck after data changes: shifts in distribution or query shape can make a previously useful index redundant or harmful.
Which engine has the “best” indexes?
There is no engine-wide winner established by these structural differences. SQL Server’s clustered-versus-heap choice, InnoDB’s primary-key clustering and secondary-key inheritance, and PostgreSQL’s range of access methods solve different physical and query problems. The defensible decision is the one that matches the deployed version, predicates, data distribution, read/write mix, and measured execution plans.
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.
