How charts get their data

Optional reading: this explains what happens out of sight. See The data centre for what this section is, and the chart components for how to use the charts.

A chart is written to work from a handle on a dataset, not from a table of rows. The handle can answer a small set of questions, and each chart asks exactly the ones it needs. There are two families: charts that draw a mark per row, and charts that draw a mark per group of rows.

Charts that group: histograms, hexbins, box plots

A histogram does not need the rows. It needs to know how many rows fall in each bar. So that is what it asks, and the database counts.

Histogram

The chart asks for the extent of the field: its smallest and largest value, over the rows that have a number. One small answer.
In the browser it works out the bar edges. If you gave a start, a step and a count in the settings, those are the edges. If not, the chart picks a bar width from the range and the number of rows by Sturges’ rule - the count the auto placeholder shows. The edges are worked out by repeatedly adding the step, so they are the same ones whatever backs the chart. (At most 1,000 bars are allowed.)
It sends the database the edges and asks for the number of rows in each interval, with optional summaries per interval - the mean, sum, median or other of a second field if you colour the bars by one. The database buckets each row into an interval and counts.
Back come the counts - about thirty numbers - and the chart draws the bars.

Rows with no value in the field, or outside every interval, are counted separately, which is where the status bar’s Missing and Out of range come from.

Because the answer is thirty numbers, drawing a histogram of ten million rows costs the browser the same as drawing one of a thousand. What grows is the database’s work, which is a scan of one column.

Hexbin plot

A hexbin groups points into hexagons, and the hexagons are defined in pixels: their size is a number of pixels, so how they tile depends on the plot’s width, its margins and its axes. The chart therefore tells the database the axes’ ranges, whether each is linear or logarithmic, the plot’s pixel ranges and the hexagon width, and the database runs the plotting library’s own hexagon arithmetic for every row, so a row cannot land in a different hexagon from the one the chart would have put it in. What comes back is a count (or a summary of another field) for each non-empty hexagon - a few hundred of them - and the chart draws each at its centre. Resizing the chart asks again after a moment, and the old hexagons stay on screen meanwhile.

Box plot

A box plot of a field within bins of another needs, for each bin, the quartiles, the whiskers and the outliers. The database computes them with the plotting library’s own rules (the same quantile definition, and the same fences, so a value is an outlier exactly when the chart would have drawn it as one) and returns the numbers for each box plus the outlying rows themselves - up to 20,000 of them, with a note on the chart if there were more. If part of the data is marked, the chart is two queries, the marked rows and the rest.

Integer scatter plot

For fields with a handful of distinct whole values, the chart makes one group per distinct pair of values and draws a circle sized by the count. This only makes sense when the values are discrete: if there would be more than 100,000 distinct positions the database refuses, and the chart tells you to use a scatter or hexbin plot instead.

Selecting in a grouped chart

Dragging across bars, hexagons or boxes selects groups, and a selection has to be a set of rows. The chart asks the database for the rows in these groups, and the database answers with their ids. So a selection that covers half of ten million rows is five million ids; see below for how that is kept manageable.

Nothing can be grouped here - each row is a point or a line - but nobody can read a million of them, and the browser cannot draw a million SVG marks. So these charts draw at most Max points of them (10,000 by default; it is on the Appearance tab, and the caption says Showing a sample of 10,000 of 1,000,000 points when it applies).

The sample is chosen by the database, by a rule on the row id, not by a random draw. A row is in the sample if a fixed arithmetic function of its id falls below the fraction wanted. That has useful consequences:

it is stable: the same rows are drawn every time, so the picture does not shuffle when you reopen it;
it is evenly spread through the dataset, whatever order the rows are in;
it is nested: a smaller sample is always a subset of a larger one, so raising Max points adds points and does not replace them;
every row that is marked is drawn regardless, so a highlight is never hidden by sampling.

Two things make the sample honest:

A selection covers every row, not just the drawn ones. A box you drag is turned into a condition on the fields (x between a and b, y between c and d) and the database finds every matching row, so the histogram you have wired to the plot sees all of them.
The axes are true. Where a chart needs the range of a field - the axes of a parallel-coordinates plot, or how far a selection extends - it asks for the extent over all rows, so a rare extreme value still sets the scale even if it was not among the points drawn.

Selections and filters across charts

When a selection from one chart becomes another chart’s filter, what travels is a list of row ids. For a small selection that is a short list in the request. Once a list has 5,000 or more ids, the browser sends it to the database once, which keeps it as a numbered set, and every later question - by this chart or any other chart showing the same selection - names the set instead of repeating it. Without that, every chart on the page would re-send hundreds of kilobytes of ids with every question. A set is kept for a while after it was last used (30 minutes), the database keeps a limited number per project, and a set whose dataset has been replaced is dropped, because its ids might then mean other rows; if a chart asks for a set that is gone, it simply makes it again.

The same idea is used when a drag selects rows directly: the database both finds the rows and keeps them as a set in one step, so their ids do not make a round trip to the browser only to be sent straight back.

Filters on the dataset

If a filter is applied to the dataset on the Data tab, every one of these questions carries an extra condition - and the row is not hidden - so every chart, the preview, an export and a join see only the rows the filter leaves. The hidden rows are not deleted: they are marked in a column of the table, which is why a filter can be changed or cleared in a moment and why making it permanent is a separate, deliberate step.