psycodict.statstable¶

Precomputed counts and statistics for a search table.

PostgresStatsTable, available as table.stats, records the number of rows matching queries (in an auxiliary _counts table) and statistics – minimum, maximum, average, totals – on numerical columns (in a _stats table), so that count displays and statistics pages need not rescan large tables. Statistics are added with add_stats and add_numstats, kept current by the data management methods of PostgresTable, and read back with count, quick_count, max, column_counts, numstats and friends.

class psycodict.statstable.PostgresStatsTable(table, total=None)[source]¶

Bases: PostgresBase

This object is used for storing statistics and counts for a search table, available as the stats attribute of a PostgresSearchTable.

For each search table (e.g. ec_curvedata), there are two auxiliary tables supporting statistics functionality. The counts table (e.g. ec_curvedata_counts) records the number of rows in the search table that satisfy a particular query. These counts are used by the website to display the number of matches on a search results page, and is also used on statistics pages and some browse pages. The stats table (e.g. ec_curvedata_stats) is used to record minimum, maximum and average values taken on by a numerical column (possibly over rows subject to some constraint).

The stats table also serves a second purpose. When displaying statistics for a section of the website, we often want to compute counts over all possible values of a set of columns. For example, we might compute the number of elliptic curves with each possible torsion structure, or statistics on the conductor norm for elliptic curves over each number field. The add_stats and add_numstats methods provide these features, and when they are called a row is added to the stats table recording that these statistics were computed.

We are only able to store counts and statistics in this way because our tables rarely change. When we do make a change, statistics need to be updated. This is done using the refresh_statistics method, which is called by default by the data management methods of PostgresTable like reload or copy_from. As a consequence, once statistics are added, they do not need to be manually updated.

The backend functionality of this object supports the StatsDisplay object available in the LMFDB (lmfdb.utils.display_stats). See that module for more details on making a statistics page for a section of the LMFDB. In particular, the interface there has the capacity to automatically call add_stats so that viewing an appropriate stats page (e.g. beta.lmfdb.org/ModularForm/GL2/Q/holomorphic/stats) is sufficient to add the necessary statistics to the stats and counts tables. The methods _get_values_counts and _get_total_avg exist to support the StatsDisplay object.

Once statistics have been added, they are accessed using the following functions:

quick_count – count the number of rows satisfying a query, returning None if not already cached.
count – count the number of rows satisfying a query, computing and storing the result if not yet cached.
max – returns the maximum value attained by a column, computing and storing the result if not yet cached.
column_counts – provides all counts stored for a given column or set of columns. This will be much faster than calling count repeatedly. If add_stats has not been called, it will do so.
numstats – provides numerical statistics on a single column, grouped by the values taken on by another set of columns.
extra_counts – returns a dictionary giving counts that were added separately from an add_stats call (for example, via user requests on the website)
status – prints a summary of the statistics currently stored.

EXAMPLES:

We add some statistics. These specific commands aren’t required in order to access stats, but they hopefully provide an example of how to add statistics that can be generalized to other tables.

Adding statistics on torsion structure:

sage: db.ec_nfcurves.stats.add_stats(['torsion_structure'])

This make counts available:

sage: db.ec_nfcurves.stats.quick_count({'torsion_structure': [2,4]})
5100
sage: torsion_structures = db.ec_nfcurves.stats.column_counts('torsion_structure')
sage: torsion_structures[4,4]
14

Adding statistics on norm_conductor, grouped by signature:

sage: db.ec_nfcurves.stats.add_numstats('norm_conductor', ['signature'])

Once added, we can later retrieve the statistics:

sage: normstats = db.ec_nfcurves.stats.numstats('conductor_norm', ['signature'])

And find the maximum conductor norm for a curve in the LMFDB over a totally real cubic field:

sage: normstats[3,0]['max']
2059

You can also find this directly, but if you need the same kind of statistic many times then the numstats method will be faster:

sage: db.ec_nfcurves.stats.max('conductor_norm', {'signature': [3,0]})
2059

You can see what additional counts are stored using the extra_counts method:

sage: list(db.mf_newforms.stats.extra_counts())[0]
('dim',)
sage: db.mf_newforms.stats.extra_counts()[('dim',)]
[(({'$gte': 10, '$lte': 20},), 39288)]

SCHEMA:

The columns in a counts table are:

cols – these are the columns specified in the query. A list, stored as a jsonb.
values – these could be numbers, or dictionaries giving a more complicated constraint. A list, of the same length as cols, stored as a jsonb.
count – the number of rows in the search table where the columns take on the given values.
extra – false if the count was added in an add_stats method, true if it was added separately (such as by a request on a search results page).
split – used when column values are arrays. If true, then the array is split up before counting. For example, when counting ramified primes, if split were false then [2,3,5] and [2,3,7] would count as separate values (there are 888280 number fields in the LMFDB with ramps = [2,3,5]). If split were true, then both [2,3,5] and [2,3,7] would contribute toward the count for 2.

For example, [“ramps”], [[2, 3, 5]], 888280, t, f would record the count of number fields with ramps=[2, 3, 5], and [“ramps”], [2], 11372999, f, t would record the count of number fields with ramps containing 2.

Rows are keyed by (cols, values, split): at most one row should be stored per key. There is no unique index enforcing this, but add_stats and add_numstats replace any existing rows with the keys they insert – whether recorded by a one-off count or by an overlapping statistics run – rather than adding duplicates.

The columns in a stats table are:

stat – a text field giving the statistic type. Currently, will be one of “max”, “min”, “avg”, “total” (one such row for each add_stats call), “ntotal” (one such row for each add_numstats call), “split_total” (one such row for each add_stats call with split_list True).
cols – the columns for which statistics are being computed. Must have length 1 and be numerical in order to have “max”, “min” or “avg”
constraint_cols – columns in the constraint dictionary
constraint_values – the values specified for the columns in ccols
threshold – NULL or an integer. If specified, only value sets where the row count surpasses the threshold will be added to the counts table and counted toward min, max and avg statistics.

BUCKETED STATS:

Sometimes you want to add statistics on a column, but it takes on too many values. For example, you want to give an idea of the distribution of levels for classical modular forms, but there are thousands of possibilities.

You can use the add_bucketed_counts in this circumstance. You provide a dictionary whose keys are columns, and whose values are a list of strings giving intervals. Counts are computed with values grouped into intervals.

EXAMPLES:

sage: db.mf_newforms.stats.add_bucketed_counts(['level', 'weight'], {'level': ['1','2-10','11-100','101-1000','1001-2000', '2001-4000','4001-6000','6001-8000','8001-10000'], 'weight': ['1','2','3','4','5-8','9-16','17-32','33-64','65-316']})

You can now count certain ranges:

sage: db.mf_newforms.stats.quick_count({'level':{'$gte':101, '$lte':1000}, 'weight':4})
12281

But only those specified by the buckets:

sage: db.mf_newforms.stats.quick_count({'level':{'$gte':201, '$lte':800}, 'weight':2}) is None
True

INPUT:

table – a PostgresTable object.
total – an integer, the number of rows in the search table. If not provided, it will be looked up or computed.

saving = False¶

quick_count(query, split_list=False, suffix='', startup=False)[source]¶

Tries to quickly determine the number of results for a given query using the count table.

INPUT:

query – a mongo-style dictionary, as in the search method.
split_list – see the add_stats method
suffix – if provided, the table with that suffix added will be
used to perform the count

OUTPUT:

Either an integer giving the number of results, or None if not cached.

null_counts(suffix='')[source]¶: Returns the columns with null values, together with the count of the number of null rows for each

refresh_null_counts(suffix='')[source]¶: Recomputes the counts of null values for all search columns

count(query={}, groupby=None, record=False)[source]¶

Count the number of results for a given query.

INPUT:

query – a mongo-style dictionary, as in the search method.
record – (default False) whether to record the number of results in the counts table. Recording is useful for queries whose counts are displayed repeatedly (search pages record theirs), but every distinct recorded query adds a row to the counts table, so it is opt-in: scripts running many one-off counts no longer clutter the table (and slow down reloads) by accident. Recording also requires saving to be enabled; with the psycodict default saving = False nothing is written.
groupby – (default None) a list of columns

OUTPUT:

If grouby is None, the number of records satisfying the query. Otherwise, a dictionary with keys the distinct tuples of values taken on by the columns in groupby, and values the number of rows with those values.

EXAMPLES:

sage: from lmfdb import db
sage: nf = db.nf_fields
sage: nf.stats.count({'degree':int(6),'galt':int(7)})
244006

quick_count_distinct(cols, query={}, suffix='')[source]¶

Tries to quickly determine the number of distinct values of a column using the stats table.

INPUT:

cols – a list of column names
query – a search query, as a dictionary
suffix – if provided, the table with that suffix added will be
used to perform the count

OUTPUT:

Either an integer giving the number of distinct values, or None if not cached.

count_distinct(col, query={}, record=False)[source]¶

Count the number of distinct values taken on by given column(s).

The result will be the same as taking the length of the distinct values, but a bit faster and can cache the answer

INPUT:

col – the name of the column, or a list of such names
query – a query dictionary constraining which rows are considered
record – (default False) whether to record the number of results in the stats table; opt-in for the same reason as in count.

column_counts(cols, constraint=None, threshold=1, split_list=False)[source]¶

Returns all of the counts for a given column or set of columns.

INPUT:

cols – a string or list of strings giving column names.
constraint – only rows satisfying this constraint will be considered.
It should take the form of a dictionary of the form used in search queries.
threshold – an integer or None. If specified, only values with
counts above the threshold are returned.
split_list – see the documentation for add_stats.

OUTPUT:

A dictionary with keys the values taken on by the columns in the database, and value the count of rows taking on those values. If threshold is provided, only counts at least the threshold will be included.

If cols is a string, then the keys of the dictionary will be just the values taken on by that column. If cols is a list of strings, then the keys will be tuples of values taken on by the dictionary.

If the value taken on by a column is a dictionary D, then the key will be tuple(D.items()). However, we omit entries where D contains only keys starting with $, since these are used to encode queries.

max(col, constraint={}, record=True)[source]¶

The maximum value attained by the given column, which must be in the search table.

Will raise an error if there are no non-null values of the column.

INPUT:

col – the column on which the max is taken.
constraint – a dictionary giving a constraint. The max will be taken
over rows satisfying this constraint.
record – whether to store the result in the stats table
(which happens only when saving is enabled).

EXAMPLES:

sage: from lmfdb import db
sage: db.nf_fields.stats.max('class_number')
1892503075117056

min(col, constraint={}, record=True)[source]¶

The minimum value attained by the given column, which must be in the search table.

Will raise an error if there are no non-null values of the column.

INPUT:

col – the column on which the min is taken.
constraint – a dictionary giving a constraint. The min will be taken
over rows satisfying this constraint.
record – whether to store the result in the stats table
(which happens only when saving is enabled).

EXAMPLES:

sage: from lmfdb import db
sage: db.ec_mwbsd.stats.min('area')
0.00000013296713869846309987200099760

sum(col, constraint={}, record=True)[source]¶

The sum of the given column over rows satisfying the constraint.

Answered from the stats table when cached; otherwise computed, and stored in the stats table when record and saving are both set.

INPUT:

col – the column to be summed, which must be in the search table.
constraint – a dictionary giving a constraint. The sum is
taken over rows satisfying this constraint.
record – whether to store the result in the stats table
(which happens only when saving is enabled).

add_bucketed_counts(cols, buckets, constraint={})[source]¶

A convenience function for adding statistics on a given set of columns, where rows are grouped into intervals by a bucketing dictionary.

See the add_stats method for the actual statistics computed.

INPUT:

cols – the columns to be displayed. This will usually be a list of strings of length 1 or 2.
buckets – a dictionary whose keys are columns, and whose values are lists
of strings giving either single integers or intervals.
constraint – a dictionary giving additional constraints on other columns.

add_numstats(col, grouping, constraint=None, threshold=None, suffix='')[source]¶

For each value taken on by the columns in grouping, numerical statistics on col (min, max, avg) will be added.

This function does not add counts of each distinct value taken on by col, and it uses SQL rather than Python to compute MIN, MAX and AVG. This makes it more suitable than add_stats if a column takes on a large number of distinct values.

INPUT:

col – the column whose minimum, maximum and average values are to be computed.
Should be an integer or real type in order for AVG to function.
grouping – a list of columns. Statistics will be computed within groups defined by
the values taken on by these columns. If no columns given, then the overall statistics will be computed.
constraint – a dictionary or pair of lists, giving a query. Only rows satisfying this
constraint will be included in the statistics.
threshold – if given, only sets of values for the grouping columns where the
count surpasses this threshold will be included.
suffix – if given, the counts will be performed on the table with the suffix appended.

numstats(col, grouping, constraint=None, threshold=None)[source]¶

Returns statistics on a column, grouped by a set of other columns.

If the statistics are not already cached, the add_numstats method will be called.

INPUT:

col – the column whose minimum, maximum and average values are to be computed.
Should be an integer or real type in order for AVG to function.
grouping – a list of columns. Statistics will be computed within groups defined by
the values taken on by these columns. If no columns given, then the overall statistics will be computed.
constraint – a dictionary or pair of lists, giving a query. Only rows satisfying this
constraint will be included in the statistics.
threshold – if given, only sets of values for the grouping columns where the
count surpasses this threshold will be included.

OUTPUT:

A dictionary with keys the possible values taken on the columns in grouping. Each value is a dictionary with keys ‘min’, ‘max’, ‘avg’

add_stats(cols, constraint=None, threshold=None, split_list=False, suffix='')[source]¶

Add statistics on counts, average, min and max values for a given set of columns.

INPUT:

cols – a list of columns, usually of length 1 or 2.
constraint – only rows satisfying this constraint will be considered.
It should take the form of a dictionary of the form used in search queries. Alternatively, you can provide a pair ccols, cvals giving the items in the dictionary.
threshold – an integer or None.
split_list – if True, then counts each element of lists separately. For example,
if the list [2,4,8] occurred as the value for a certain column, the counts for 2, 4 and 8 would each be incremented. Constraint columns are not split. This option is not supported for nontrivial thresholds.
suffix – if given, the counts will be performed on the table with the suffix appended.

OUTPUT:

Counts for each distinct tuple of values will be stored, as long as the number of rows sharing that tuple is above the given threshold. If there is only one column and it is numeric, average, min, and max will be computed as well.

Existing rows of the counts and stats tables with the same keys as the fresh rows (for example one-off counts previously recorded from a search results page) are replaced instead of duplicated.

Returns a boolean: whether any counts were stored.

add_stats_auto(cols=None, constraints=[None], max_depth=None, threshold=1000)[source]¶

Searches for combinations of columns with many rows having the same set of values.

The main application is determining which indexes might be useful to add.

INPUT:

cols – a set of columns. If not provided, columns where the most common value has at least 700 rows will be used.
constraints – a list of constraints. Statistics will be added for each set of constraints.
max_depth – the maximum number of columns to include
threshold – only counts above this value will be included.

refresh_stats(total=True, reset_None_to_1=False, suffix='')[source]¶

Regenerate stats and counts, using rows with stat = "total" in the stats table to determine which stats to recompute, and the rows with extra = True in the counts table which have been added by user searches.

INPUT:

total – if False, doesn’t update the total count (since we can often
update the total cheaply)
reset_None_to_1 – change threshold None to 1 in stored statistics
suffix – appended to the table name when computing and storing stats.
Used when reloading a table.

status(reset_None_to_1=False)[source]¶: Prints a status report on the statistics for this table.

extra_counts(include_counts=True, suffix='')[source]¶

Returns a dictionary of the extra counts that have been added by explicit count calls that were not included in counts generated by add_stats.

The keys are tuples giving the columns being counted, the values are lists of pairs, where the first entry is the tuple of values and the second is the count of rows with those values. Note that sometimes the values could be dictionaries giving more complicated search queries on the corresponding columns.

INPUT:

include_counts – if False, will omit the counts and just give lists of values.
suffix – Used when dealing with _tmp or _old* tables.

create_oldstats(filename)[source]¶: Temporary support for statistics created in Mongo.

get_oldstat(name)[source]¶: Temporary support for statistics created in Mongo.