Thread

BUG #19114: ORDER BY ASC is tampering result when calculating distance btw vectors

PG Bug reporting form <noreply@postgresql.org> — 2025-11-17T06:08:30Z

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.