1-50 of 508 found
DISTINCT That Doesn’t Recount
DISTINCT ON PostgreSQL’s DISTINCT ON is a different feature from DISTINCT. It returns one row per group, ordered by a specified column: SELECT DISTINCT ON (customer_id) customer_id, order_id, total…
Bitmap Distinct Estimator
Bitmap Distinct Estimator This is an implementation of self-learning bitmap, as described in the paper “Distinct Counting with a Self-Learning Bitmap” (by Aiyou Chen and Jin Cao, published in 2009).
Adaptive Distinct Estimator
Adaptive Distinct Estimator This is an implementation of Adaptive Sampling algorithm presented in paper “On Adaptive Sampling” pub. in 1990 (written by P. Flajolet).
Distinct Parent Sort (Join Explosion)
Distinct Parent Sort (Join Explosion) Join: Single Feature (Local Field with Deduplication) Description: The user wants to find users that match criteria in the comments table (a “Many” side join)…
Search-by-key-and-value
… that once had a certain value by using the @> operator: SELECT DISTINCT audit_id FROM pgmemento.row_log WHERE old_data @> '{"column_B": "old_value"}'::jsonb;
Search-by-key-and-value
… that once had a certain value by using the @> operator: SELECT DISTINCT audit_id FROM pgmemento.row_log WHERE old_data @> '{"column_B": "old_value"}'::jsonb;
Search-by-key-and-value
… that once had a certain value by using the @> operator: SELECT DISTINCT audit_id FROM pgmemento.row_log WHERE old_data @> '{"column_B": "old_value"}'::jsonb;
Search-by-key-and-value
… that once had a certain value by using the @> operator: SELECT DISTINCT audit_id FROM pgmemento.row_log WHERE old_data @> '{"column_B": "old_value"}'::jsonb;
Search-by-key-and-value
… that once had a certain value by using the @> operator: SELECT DISTINCT audit_id FROM pgmemento.row_log WHERE old_data @> '{"column_B": "old_value"}'::jsonb;
Examples
… a table Exact count distinct: $ time psql test -c "select count(distinct id) from random_ints_100m;" count ---------- 63208457 (1 row) real 1m59.
Search-by-key
… column of a given row (audit_id) by using the ? operator: SELECT DISTINCT e.transaction_id FROM pgmemento.table_event_log e JOIN pgmemento.row_log r ON r.event_key = e.event_key WHERE r.
Search-by-key
… column of a given row (audit_id) by using the ? operator: SELECT DISTINCT e.transaction_id FROM pgmemento.table_event_log e JOIN pgmemento.row_log r ON r.event_key = e.event_key WHERE r.
Search-by-key
… column of a given row (audit_id) by using the ? operator: SELECT DISTINCT e.transaction_id FROM pgmemento.table_event_log e JOIN pgmemento.row_log r ON r.event_key = e.event_key WHERE r.
Search-by-key
… column of a given row (audit_id) by using the ? operator: SELECT DISTINCT e.transaction_id FROM pgmemento.table_event_log e JOIN pgmemento.row_log r ON r.event_key = e.event_key WHERE r.
Search-by-key
… column of a given row (audit_id) by using the ? operator: SELECT DISTINCT e.transaction_id FROM pgmemento.table_event_log e JOIN pgmemento.row_log r ON r.event_key = e.event_key WHERE r.
cardinality
sql is retained as a representative baseline for COUNT(DISTINCT ...).
v0.9.0 — Algebraic Aggregate Maintenance
COUNT(DISTINCT) Fast Path Counting distinct values (COUNT(DISTINCT customer_id)) is one of the harder aggregates to maintain incrementally, because you need to know whether a removed value was the…
PLAN: Multi-Table Delta Batching (B-3)
… causes silent data corruptiPreviously, it was proposed to use DISTINCT ON for cross-delta deduplication.This causes silent data corrupstPreviously, it was proposed to use DISTINCT ON for…
Reverting-multiple-transactions
For each distinct audit_it only the oldest table operation is applied to make the revert process faster. It is also provided for transaction ranges. SELECT pgmemento.
Reverting-multiple-transactions
For each distinct audit_it only the oldest table operation is applied to make the revert process faster. It is also provided for transaction ranges. SELECT pgmemento.
Reverting-multiple-transactions
For each distinct audit_it only the oldest table operation is applied to make the revert process faster. It is also provided for transaction ranges. SELECT pgmemento.
Reverting-multiple-transactions
For each distinct audit_it only the oldest table operation is applied to make the revert process faster. It is also provided for transaction ranges. SELECT pgmemento.
Reverting-multiple-transactions
For each distinct audit_it only the oldest table operation is applied to make the revert process faster. It is also provided for transaction ranges. SELECT pgmemento.
after-m1-m-q3
v)), (count(DISTINCT current_keyword.e))::text Group Key: ((':'::text || current_keyword.v)) InitPlan 1 (returns $0) -> Seq Scan on mentat.schema (cost=0.00..1.49 rows=1 width=8) Output: schema.
after-m1-s-q3
v)), (count(DISTINCT current_keyword.e))::text Group Key: ((':'::text || current_keyword.v)) InitPlan 1 (returns $0) -> Seq Scan on mentat.schema (cost=0.00..1.49 rows=1 width=8) Output: schema.
before-190-m-q3
v)), (count(DISTINCT current_keyword.e))::text Group Key: ((':'::text || current_keyword.v)) InitPlan 1 (returns $0) -> Seq Scan on mentat.schema (cost=0.00..1.49 rows=1 width=8) Output: schema.
before-190-s-q3
v)), (count(DISTINCT current_keyword.e))::text Group Key: ((':'::text || current_keyword.v)) InitPlan 1 (returns $0) -> Seq Scan on mentat.schema (cost=0.00..1.49 rows=1 width=8) Output: schema.
TopK Aggregate on JOIN, Low Selectivity (DataFusion)
… of its owner’s badges, so the aggregate sees far more rows than distinct names, and every row carries a real string.Query Info (statistics from 20m dataset): ‘javascript’ selectivity on…
TopK Aggregate on JOIN, Medium Selectivity (DataFusion)
… of its owner’s badges, so the aggregate sees far more rows than distinct names, and every row carries a real string.Query Info (statistics from 20m dataset): ‘regex’ selectivity on…
Argm
… key_1, key_2) FROM some_table GROUP BY gr is equivalent to DISTINCT ON clause SQL SELECT DISTINCT ON (gr) value FROM some_table ORDER BY gr, key_1 DESC, key_2 DESC but there are the following pros…
pg-aggregate-s-c1-r1
v_bytes, 'hex') ELSE NULL::TEXT END, COUNT(DISTINCT da WARNING: mentat slow query (125.6ms > 100ms threshold): datalog=[:find ?state (count ?i) :where [?i :issue/state ?state]], sql=SELECT CASE…
pg-aggregate-s-c1-r2
v_bytes, 'hex') ELSE NULL::TEXT END, COUNT(DISTINCT da WARNING: mentat slow query (125.9ms > 100ms threshold): datalog=[:find ?state (count ?i) :where [?i :issue/state ?state]], sql=SELECT CASE…
pg-aggregate-s-c1-r3
v_bytes, 'hex') ELSE NULL::TEXT END, COUNT(DISTINCT da WARNING: mentat slow query (125.8ms > 100ms threshold): datalog=[:find ?state (count ?i) :where [?i :issue/state ?state]], sql=SELECT CASE…
pg-ref_traversal-xl-c1-r1
… [?i :issue/title ?title] [?i :issue/state ?state]], sql=SELECT DISTINCT datoms1.e::TEXT, CASE datoms2.value_type_tag WHEN 0 THEN datoms2.v_ref::TEXT WHEN 1 THEN datoms2.
pg-aggregate-m-c1-r1
v_bytes, 'hex') ELSE NULL::TEXT END, COUNT(DISTINCT da WARNING: mentat slow query (1535.5ms > 100ms threshold): datalog=[:find ?state (count ?i) :where [?i :issue/state ?state]], sql=SELECT CASE…
pg-aggregate-m-c1-r3
v_bytes, 'hex') ELSE NULL::TEXT END, COUNT(DISTINCT da WARNING: mentat slow query (1546.4ms > 100ms threshold): datalog=[:find ?state (count ?i) :where [?i :issue/state ?state]], sql=SELECT CASE…
pg-predicate_scan-l-c1-r1
… [?i :issue/title ?title] [(>= ?priority ?min)]], sql=SELECT DISTINCT datoms0.e::TEXT, CASE datoms2.value_type_tag WHEN 0 THEN datoms2.v_ref::TEXT WHEN 1 THEN datoms2.
pg-predicate_scan-l-c1-r2
… [?i :issue/title ?title] [(>= ?priority ?min)]], sql=SELECT DISTINCT datoms0.e::TEXT, CASE datoms2.value_type_tag WHEN 0 THEN datoms2.v_ref::TEXT WHEN 1 THEN datoms2.
pg-predicate_scan-l-c1-r3
… [?i :issue/title ?title] [(>= ?priority ?min)]], sql=SELECT DISTINCT datoms0.e::TEXT, CASE datoms2.value_type_tag WHEN 0 THEN datoms2.v_ref::TEXT WHEN 1 THEN datoms2.
pg-concurrency_sweep-xl-c64-r2
… [?i :issue/title ?title] [?i :issue/state ?state]], sql=SELECT DISTINCT datoms1.e::TEXT, CASE datoms2.value_type_tag WHEN 0 THEN datoms2.v_ref::TEXT WHEN 1 THEN datoms2.
pg-aggregate-m-c1-r2
v_bytes, 'hex') ELSE NULL::TEXT END, COUNT(DISTINCT da WARNING: mentat slow query (1513.8ms > 100ms threshold): datalog=[:find ?state (count ?i) :where [?i :issue/state ?state]], sql=SELECT CASE…
pg-predicate_scan-m-c1-r1
… [?i :issue/title ?title] [(>= ?priority ?min)]], sql=SELECT DISTINCT datoms0.e::TEXT, CASE datoms2.value_type_tag WHEN 0 THEN datoms2.v_ref::TEXT WHEN 1 THEN datoms2.
pg-predicate_scan-m-c1-r2
… [?i :issue/title ?title] [(>= ?priority ?min)]], sql=SELECT DISTINCT datoms0.e::TEXT, CASE datoms2.value_type_tag WHEN 0 THEN datoms2.v_ref::TEXT WHEN 1 THEN datoms2.
pg-predicate_scan-m-c1-r3
… [?i :issue/title ?title] [(>= ?priority ?min)]], sql=SELECT DISTINCT datoms0.e::TEXT, CASE datoms2.value_type_tag WHEN 0 THEN datoms2.v_ref::TEXT WHEN 1 THEN datoms2.
pg-sustained-xl
… :where [?e :user/email ?email] [?e :user/name ?name]], sql=SELECT DISTINCT datoms0.e::TEXT, CASE datoms1.value_type_tag WHEN 0 THEN datoms1.v_ref::TEXT WHEN 1 THEN datoms1.
Querying with Provenance
… in SELECT Recursive CTEs (WITH RECURSIVE) INTERSECT DISTINCT ON GROUPING SETS, CUBE, ROLLUP Operations on aggregate results requiring comparison or duplicate elimination: DISTINCT on aggregates…
Aggregation and Grouping
SELECT DISTINCT SELECT DISTINCT is modelled as a GROUP BY on all selected columns. Each distinct output row gets a provenance token that captures all the duplicate source rows that were merged…
pg-aggregate-l-c1-r1
v_bytes, 'hex') ELSE NULL::TEXT END, COUNT(DISTINCT da WARNING: mentat slow query (16200.2ms > 100ms threshold): datalog=[:find ?state (count ?i) :where [?i :issue/state ?state]], sql=SELECT CASE…
pg-aggregate-l-c1-r2
v_bytes, 'hex') ELSE NULL::TEXT END, COUNT(DISTINCT da WARNING: mentat slow query (16342.3ms > 100ms threshold): datalog=[:find ?state (count ?i) :where [?i :issue/state ?state]], sql=SELECT CASE…
pg-aggregate-l-c1-r3
v_bytes, 'hex') ELSE NULL::TEXT END, COUNT(DISTINCT da WARNING: mentat slow query (16384.9ms > 100ms threshold): datalog=[:find ?state (count ?i) :where [?i :issue/state ?state]], sql=SELECT CASE…