ddsketch
ddsketch
ddsketch : Provides ddsketch aggregate function
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 4650 | ddsketch | ddsketch | 1.0.1 | FUNC | PostgreSQL | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d-r | No | Yes | No | Yes | yes | no |
| Relationships | |
|---|---|
| See Also | omnisketch quantile lower_quantile topn count_distinct hll first_last_agg |
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PIGSTY | 1.0.1 | 18 17 16 15 14 | ddsketch | - |
| RPM | PIGSTY | 1.0.1 | 18 17 16 15 14 | ddsketch_$v | - |
| DEB | PIGSTY | 1.0.1 | 18 17 16 15 14 | postgresql-$v-ddsketch | - |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
el8.aarch64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
el9.x86_64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
el9.aarch64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
el10.x86_64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
el10.aarch64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
d12.x86_64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
d12.aarch64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
d13.x86_64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
d13.aarch64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
u22.x86_64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
u22.aarch64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
u24.x86_64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
u24.aarch64 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 | PIGSTY 1.0.1 |
Source
pig build pkg ddsketch; # build rpm/debInstall
Make sure PGDG and PIGSTY repo available:
pig repo add pgsql -u # add both repo and update cacheInstall this extension with pig:
pig install ddsketch; # install via package name, for the active PG version
pig install ddsketch -v 18; # install for PG 18
pig install ddsketch -v 17; # install for PG 17
pig install ddsketch -v 16; # install for PG 16
pig install ddsketch -v 15; # install for PG 15
pig install ddsketch -v 14; # install for PG 14Create this extension with:
CREATE EXTENSION ddsketch;Usage
Implements DDSketch, a fully-mergeable quantile sketch with relative-error guarantees. Much faster than percentile_cont and supports parallelism.
CREATE EXTENSION ddsketch;Direct Aggregation Functions
| Function | Description |
|---|---|
ddsketch_percentile(value, alpha, nbuckets, quantile) | Estimate a single percentile |
ddsketch_percentile(value, alpha, nbuckets, quantiles[]) | Estimate multiple percentiles |
ddsketch_percentile_of(value, alpha, nbuckets, value) | Estimate percentile rank of a value |
ddsketch_percentile_of(value, alpha, nbuckets, values[]) | Estimate percentile ranks of multiple values |
Pre-aggregation Functions
| Function | Description |
|---|---|
ddsketch(value, alpha, nbuckets) | Build a ddsketch from values |
ddsketch_percentile(sketch, quantile) | Estimate percentile from a pre-built sketch |
ddsketch_percentile(sketch, quantiles[]) | Estimate multiple percentiles from a pre-built sketch |
Utility Functions
| Function | Description |
|---|---|
ddsketch_count(sketch) | Return the number of items in the sketch |
ddsketch_sum(sketch, low, high) | Trimmed sum within a value range |
ddsketch_avg(sketch, low, high) | Trimmed average within a value range |
Parameters
alpha– controls accuracy and sketch size (lower = more accurate, larger)nbuckets– maximum number of buckets (each 8 bytes)
Examples
-- Instead of: SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY a) FROM t;
SELECT ddsketch_percentile(a, 0.05, 1024, 0.95) FROM t;
-- Multiple percentiles at once
SELECT ddsketch_percentile(a, 0.05, 1024, ARRAY[0.5, 0.95, 0.99]) FROM t;
-- Pre-aggregate for fast repeated queries
CREATE TABLE p AS SELECT a, b, ddsketch(c, 0.05, 1024) AS d FROM t GROUP BY a, b;
-- Query pre-aggregated data (~1.5ms vs ~7s for exact)
SELECT a, ddsketch_percentile(d, 0.95) FROM p GROUP BY a ORDER BY a;Last updated on