Why does an analytical query read data differently?
An analytical query is a question about many rows at once, such as the total sales of a shop last month. A lookup asks about one row, such as the order with a given number.
The two jobs need the data laid out in different ways. Knowing which layout a database uses tells you why some queries are fast and others are slow.
By the end of this lesson
Note
I can say why an analytical query reads a table differently from a lookup.
Key words
| Word | What it means | Example |
|---|---|---|
| Analytical query | a question about many rows, answered with totals or averages | |
| Lookup | a question about one row, found by its key | |
| Row store | a table kept one whole row after another | |
| Column store | a table kept one whole column after another | |
| Aggregate | a function that turns many values into one | sum(units) |
The cells students fill in are left blank in this preview.
A lookup finds one row, analysis reads many
A shop till runs lookups all day. It finds one product by its code, or changes one order. It touches a few rows and wants every column of them.
A manager asks something else: how many units did we sell in March? That question has to read every sale, but it only needs the units column. Databases built for this second job are called analytical.
Try it: total units sold
-- Add up one column over every row.
select sum(units) as total_units from sales;
-- Change it to count the rows, count(*), and run it again.
Row stores keep rows, column stores keep columns
A disk or a memory holds a long line of values. The database decides the order of that line.
A row store writes the first row, then the second, and so on. A whole row sits side by side, so a lookup finds all of it in one place.
A column store writes all the ids, then all the days, then all the shops. DuckDB works this way, so it is built for analytical queries.
Worked example: three rows in two layouts
| Layout | Order the values are written in |
|---|---|
| Row store | 1, Leeds, 12 | 2, Leeds, 8 | 3, York, 5 |
| Column store | ids: 1, 2, 3 | shops: Leeds, Leeds, York | units: 12, 8, 5 |
| Sum of units, row store | Skip across every row to pick out 12, 8 and 5 |
| Sum of units, column store | Read the units group only: 12, 8, 5 |
The cells students fill in are left blank in this preview.
A column store reads only the columns named
Take a sales table with 10 columns and one million rows. Say each value takes 8 bytes. One column is about 8 megabytes, and the whole table is about 80.
To total one column, a column store reads that one group, about 8 megabytes. A row store has to step through all 80. Real systems also squeeze the data, but the ratio stays the same.
Worked example: how much a query reads
| Query | Columns it names | Column store reads |
|---|---|---|
| select sum(units) from sales | 1 of 10 | about 8 MB |
| select shop, sum(units) from sales group by shop | 2 of 10 | about 16 MB |
| select * from sales | 10 of 10 | about 80 MB |
The cells students fill in are left blank in this preview.
Try it: units and takings by product
-- Units and takings for each product.
select product,
sum(units) as units,
sum(units * price) as takings
from sales
group by product
order by product;
-- Add a where line so only the shop York is counted.
A common mistake
Warning
Many people think select * costs the same as naming one column. In a column store it reads every column. Name the columns you need, and the database skips the rest.
Going further
Work out which columns the query select shop, sum(units * price) from sales group by shop needs. Count them, then say what share of a ten column table a column store reads.
Then explain why a till that changes one order at a time is a poor fit for a column store.