Todos os livros

Profissional

Apps Sobre Coach Entrar Começar a ler

Quantitative Finance · Glossário

O que é B-tree index, query plan?

Também chamado de: B-tree index · query plan

Definition 25.5 Research, Data and Risk Platforms · Capítulo 25 — Databases and SQL

A B-tree index keeps a copy of chosen columns of a table in a balanced sorted tree with pointers to the rows, so that rows with given values or in a range are found in logarithmic time instead of by reading the table. A query plan is the sequence of operations the database chooses to answer a query — which tables it scans, which indexes it searches, how it joins and groups — and can be displayed before the query runs.

Measured query times over 500 008 trades (log scale; one core, in memory, median of five runs, on an otherwise idle machine). The index on (account, instrument) takes the point query from 13.7 ms to 0.01 ms; the index on valid time barely helps any query, because Tuesday 18:00 still selects two days of five; DuckDB runs the report 15 times and the analytical query 53 times faster than SQLite without any index. Data: bench_tradedb.py, measured_queries.csv.
Figure 25.2. Measured query times over 500 008 trades (log scale; one core, in memory, median of five runs, on an otherwise idle machine). The index on (account, instrument) takes the point query from 13.7 ms to 0.01 ms; the index on valid time barely helps any query, because Tuesday 18:00 still selects two days of five; DuckDB runs the report 15 times and the analytical query 53 times faster than SQLite without any index. Data: bench_tradedb.py, measured_queries.csv.
Ler no capítulo →