Tous les livres

Professionnel

Applis À propos Coach Connexion Commencer la lecture

Quantitative Finance · Glossaire

Qu'est-ce que « B-tree index, query plan » ?

Aussi appelé : B-tree index · query plan

Definition 25.5 Research, Data and Risk Platforms · Chapitre 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.
Lire dans le chapitre →