From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: naveenkrishna.s@sky.uk
Subject: BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors
Date: Mon, 17 Nov 2025 06:08:30 +0000
Message-ID: <19114-b0d2502c853fe210@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19114
Logged by: Naveen Krishna S
Email address: naveenkrishna.s@sky.uk
PostgreSQL version: 14.13
Operating system: Mac OS
Description:
In a table with vector column,
SELECT embedding <=> CAST('[0.01, 0.23, -0.1,..]' as vector)
AS distance
FROM my_table
WHERE TRUE
order by distance desc
LIMIT 100;
is giving 100 records whereas
SELECT embedding <=> CAST('[0.01, 0.23, -0.1,..]' as vector)
AS distance
FROM my_table
WHERE TRUE
order by distance asc
LIMIT 100;
is giving 40 records for a specific embedding and always less than the limit
for any query embedding. Why is this?
I have also noticed if I use NULLS FIRST or COALESCE(distance, 99999999999)
it is returning the requested limit. But when I tried to list the records
with distance as null, there were none.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, naveenkrishna.s@sky.uk
Subject: Re: BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors
In-Reply-To: <19114-b0d2502c853fe210@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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