pg.ddx.io  pgvector/pgvector GitHub issues and pull requests  
help / color / mirror / Atom feed
From: leemeii (@leemeii) <noreply+leemeii@github.com>
To: pgvector/pgvector <noreply+pgvector-pgvector@github.com>
Subject: [pgvector/pgvector] issue #1066: sum(halfvec) accumulates significant rounding errors and reverses exact L2 nearest-neighbor rankings
Date: Wed, 18 Nov 2026 00:45:19 +0000
Message-ID: <gh-pgvector-pgvector-1066@github.com> (raw)

## Summary

The `sum(halfvec)` aggregate in pgvector 0.8.7 accumulates significant floating-point rounding errors when processing ordinary finite half-precision vectors.

In the reproduction below, summing 1,000 identical `halfvec(2)` values produces results that deviate substantially from the mathematically expected sums. When the input vectors and target vectors undergo a common translation, the accumulated rounding errors cause the exact L2 nearest-neighbor ranking to reverse.

The issue occurs with sequential scans, without approximate indexes or parallel execution.

This appears to be a numerical stability limitation of the half-precision aggregate implementation.

## Environment

- PostgreSQL: 18.6
- pgvector: 0.8.7
- Vector type: `halfvec(2)`
- Distance metric: L2 (`<->`)
- Execution: Sequential scan, parallel execution disabled

## Steps to Reproduce

Execute the following SQL using `psql`:

```sql
\set ON_ERROR_STOP on
\pset pager off

SET max_parallel_workers_per_gather = 0;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_seqscan = on;

DROP TABLE IF EXISTS
    pg_temp.half_agg_seed,
    pg_temp.half_agg_mutant,
    pg_temp.targets_seed,
    pg_temp.targets_mutant;

CREATE TEMP TABLE half_agg_seed (
    id int,
    v halfvec(2)
);

CREATE TEMP TABLE half_agg_mutant (
    id int,
    v halfvec(2)
);

CREATE TEMP TABLE targets_seed (
    id int,
    v halfvec(2)
);

CREATE TEMP TABLE targets_mutant (
    id int,
    v halfvec(2)
);

-- Seed: 1,000 identical vectors
INSERT INTO half_agg_seed
SELECT g, '[0.3,0.4]'::halfvec
FROM generate_series(1,1000) g;

-- Mutant: common translation b = [0.1,-0.2]
INSERT INTO half_agg_mutant
SELECT g, '[0.4,0.2]'::halfvec
FROM generate_series(1,1000) g;

-- Seed targets
INSERT INTO targets_seed VALUES
    (1, '[274.25,420.75]'),
    (2, '[364.25,400.75]');

-- Mutant targets: translated by 1000*b = [100,-200]
INSERT INTO targets_mutant VALUES
    (1, '[374.25,220.75]'),
    (2, '[464.25,200.75]');

-- Inspect aggregate results
SELECT 'seed' AS phase, sum(v) AS aggregate_result
FROM half_agg_seed;

SELECT 'mutant' AS phase, sum(v) AS aggregate_result
FROM half_agg_mutant;

-- Seed: exact L2 nearest-neighbor ranking
WITH q AS (
    SELECT sum(v) AS q
    FROM half_agg_seed
)
SELECT
    'seed' AS phase,
    t.id,
    t.v <-> q.q AS distance
FROM targets_seed t
CROSS JOIN q
ORDER BY distance, t.id;

-- Mutant: exact L2 nearest-neighbor ranking
WITH q AS (
    SELECT sum(v) AS q
    FROM half_agg_mutant
)
SELECT
    'mutant' AS phase,
    t.id,
    t.v <-> q.q AS distance
FROM targets_mutant t
CROSS JOIN q
ORDER BY distance, t.id;

-- Check whether ranking is preserved
WITH
s AS (
    SELECT array_agg(t.id ORDER BY t.v <-> q.q, t.id) AS ids
    FROM targets_seed t
    CROSS JOIN (
        SELECT sum(v) AS q FROM half_agg_seed
    ) q
),
m AS (
    SELECT array_agg(t.id ORDER BY t.v <-> q.q, t.id) AS ids
    FROM targets_mutant t
    CROSS JOIN (
        SELECT sum(v) AS q FROM half_agg_mutant
    ) q
)
SELECT
    s.ids AS seed_ids,
    m.ids AS mutant_ids,
    s.ids = m.ids AS relation_holds
FROM s, m;

DROP TABLE
    half_agg_seed,
    half_agg_mutant,
    targets_seed,
    targets_mutant;
```

## Expected Behavior

Mathematically, summing the 1,000 input vectors should produce:

```text
Seed:   [300,400]
Mutant: [400,200]
```

Let:

- `A` be the original input vectors.
- `b = [0.1,-0.2]` be the common translation.
- `n = 1000`.
- `q = sum(A)`.
- `q' = sum(A+b)`.
- `y' = y+n*b` be the translated target.

Under exact arithmetic:

```text
q' = q + n*b
y' = y + n*b

||q' - y'||₂ = ||q - y||₂
```

Therefore, the common translation should preserve all L2 distances and the nearest-neighbor ranking.

Expected ranking:

```text
Seed IDs:       [1,2]
Mutant IDs:     [1,2]
relation_holds: true
```

This expectation describes the mathematical reference behavior; finite-precision aggregation does not necessarily guarantee exact translation invariance.

## Actual Behavior

The database returns the following aggregate results:

```text
Seed aggregate:
[274.25,420.75]

Mutant aggregate:
[420.75,210.375]
```

The resulting exact L2 distances are:

**Seed**

| ID | Distance |
|---|---|
| 1 | 0 |
| 2 | 92.19544457292888 |

Ranking: `[1,2]`

**Mutant**

| ID | Distance |
|---|---|
| 2 | 44.55211134166371 |
| 1 | 47.643369160881136 |

Ranking: `[2,1]`

The relation check returns:

```text
 seed_ids | mutant_ids | relation_holds
----------+------------+---------------
 {1,2}    | {2,1}      | f
```

The behavior was observed consistently across five independent executions.

The aggregate results deviate substantially from their exact mathematical sums. For example, the first coordinate of the Seed aggregate is `274.25` instead of `300`, an absolute difference of `25.75`.


Message-ID: <gh-pgvector-pgvector-1066@github.com>
Permalink:  ../gh-pgvector-pgvector-1066@github.com/

 · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox