A database index is an additional data structure that a database maintains to help locate rows or provide a useful traversal order. In a quant data pipeline, a suitable index can make a request for one instrument's trades cheaper than inspecting an entire trade history. It adds an access path: a way to reach the requested data.
The SQL query still specifies which answer is required. The database's query planner chooses how to obtain it. Creating an index offers the planner another option; it does not force that option or promise a faster query. PostgreSQL's index introduction explains this distinction and the maintenance cost.
Same rows, different ways to find them
Consider three fictional trades. Prices are integer USD per share; IDs identify rows and are not timestamps.
| id | symbol | price |
|---|---|---|
| 1 | A | 100 |
| 2 | B | 50 |
| 3 | A | 110 |
A table scan can inspect each row and test whether
symbol = 'A'. An index on symbol can instead lead the engine to the matching entries and then to the required table rows. In an ordinary SQLite rowid table, such an index contains the indexed values and row identifiers. If an index contains every field the query needs, it is covering for that query and can avoid those table lookups. These are alternative mechanisms for producing the same requested result. SQLite's query-planning guide describes the structures.
Original schematic, not a query-plan trace or speed comparison. The index shows logical symbol-to-row entries; prices are USD per share. The query explicitly requests ascending ID order.
This Python example runs SQLite in memory, creates the index and retrieves A's trades. The
? placeholder binds a value separately from SQL syntax, as recommended in the Python sqlite3 documentation.import sqlite3
connection = sqlite3.connect(":memory:")
connection.execute(
"CREATE TABLE trades "
"(id INTEGER PRIMARY KEY, symbol TEXT NOT NULL, price INTEGER NOT NULL)"
)
connection.executemany(
"INSERT INTO trades VALUES (?, ?, ?)",
[(1, "A", 100), (2, "B", 50), (3, "A", 110)],
)
connection.execute("CREATE INDEX trades_symbol ON trades(symbol)")
query = "SELECT id, price FROM trades WHERE symbol = ? ORDER BY id"
print(connection.execute(query, ("A",)).fetchall())
# [(1, 100), (3, 110)]WHERE selects rows; ORDER BY id defines their output order. An index alone is not an output-order guarantee. Without ORDER BY, row order is undefined; if the ordering columns contain ties, add a unique tie-breaker when deterministic order matters. See SQLite's ORDER BY contract.Design for the predicate and its order
A predicate is a condition such as
symbol = 'A' or a bounded timestamp range. A useful index must fit the operators and expressions the query uses. A B-tree index supports ordered comparisons; a hash index supports a different set of operations. “Indexed” does not describe one universal search algorithm. PostgreSQL's index-type documentation sets out those differences.For a trade-history query that fixes
symbol, filters a timestamp interval and requests timestamp order, a composite B-tree index on (symbol, timestamp) is a natural candidate. It groups entries by symbol, then orders them by timestamp within each symbol. Reversing the columns to (timestamp, symbol) changes that organization. A timestamp-only query does not have the same straightforward leading-column search as the symbol-plus-time query; specialized strategies such as skip scans may still be available. Column order is therefore a workload decision, not a cosmetic choice. See SQLite's predicate rules and PostgreSQL's multicolumn-index discussion.Even a tree search is not a promise that the whole query takes logarithmic time. Returning many matches, fetching table rows, sorting and transferring the result all add work. A full scan can be sensible for a tiny table or a query returning much of it.
Check the plan, then measure the workload
SQLite's
EXPLAIN QUERY PLAN reports the chosen access strategy. Immediately after the index creation above, this example produced the following detail on Python 3.14.4 with SQLite 3.53.0:plan = connection.execute("EXPLAIN QUERY PLAN " + query, ("A",)).fetchall()
print([step[3] for step in plan])
# ['SEARCH trades USING INDEX trades_symbol (symbol=?)']
connection.execute("ANALYZE")
plan = connection.execute("EXPLAIN QUERY PLAN " + query, ("A",)).fetchall()
print([step[3] for step in plan])
# ['SCAN trades']
connection.close()After
ANALYZE collected statistics, this runtime chose a scan for the same three-row table. That is an observed plan change, not a timing result or a guarantee for other versions and data. SEARCH describes visiting a subset; SCAN can describe a table scan or an index traversal, so read the full detail. SQLite warns that plan-output formatting can change between releases.Keep the trade-off concrete: indexes consume storage, and inserts, deletes and relevant updates require maintenance. More or wider indexes can help reads while making ingestion more expensive. Evaluate representative data, selection rates and read/write patterns; inspect the plan and measure elapsed time rather than inferring speed from the presence of an index.
To connect indexing to a price-data workflow, SQL and Persistent Data builds a composite instrument-date key, a date index and a bounded price-panel query. The full lesson requires membership; a public preview is available.
Sources
- SQLite — Query Planning: index lookups, covering indexes and searching with sorting.
- SQLite — Query Optimizer Overview: WHERE clause analysis.
- SQLite — EXPLAIN QUERY PLAN.
- SQLite — SELECT: ORDER BY.
- SQLite — CREATE INDEX.
- Python — sqlite3: parameter binding and SQL execution.
- PostgreSQL 18 — Introduction to indexes, index types and multicolumn indexes.