On Wed, 7 Oct 2026 at 13:44, Greg Burd <[email protected]> wrote: > > > On Tuesday, October 6th, 2026 at 5:57 PM, Matthias van de Meent > <[email protected]> wrote: > > On Tue, 6 Oct 2026 at 18:29, Greg Burd <[email protected]> wrote: > > > > > > On Monday, October 5th, 2026 at 2:14 PM, Greg Burd <[email protected]> wrote: > > > > > > > > I've not yet measured performance or memory difference due to this > > > > change, > > > > net should be better but I should quantify that at some point. > > > > > > I've run a test in EC2 to answer this question, details at the end but > > > this > > > change is significant for some important shapes. [...] > > > What this means is a) this is a good performance win, and b) I now need > > > to see > > > if it makes sense to adjust the costing of this pattern given that some > > > shapes > > > are ~97% faster now. > > Do you have the details on what was tested to get to these results? > > And how it came to be that the results are 97% faster? > > > > The index still produces the projected distance output, which > > shouldn't be much cheaper for the index than a plan-level operator > > evaluation. Assuming 1 projection per row inside the index (which is > > reasonable for any ANN index I'm aware of) the theoretical savings are > > at most 66% (= 1 - (2/3), because you save one projection for > > xs_recheckorderby, and one projection for the plan node ORDER BY > > output column; the one inside the index remains). Or did I miss > > something -- is detoasting such an expensive factor here? > > Your model is right for an index that computes the distance at scan > time, which is what pgvector's HNSW does. The 97% case is an expression > index: > > CREATE INDEX ON vecs USING gist (point(vdist(v, qv()), 0)); > SELECT id, point(vdist(v, qv()), 0) <-> point(0,0) FROM vecs > ORDER BY point(vdist(v, qv()), 0) <-> point(0,0) LIMIT 10000; > > vdist() is a 128-dim SQL L2 distance. It runs once per row at CREATE > INDEX and the result is stored; at scan time GiST only computes point > <-> point on the stored key. So the index's "projection" is the cheap > operator, and the expensive function is evaluated zero times instead of > once per returned row. The cost moved to build time, it didn't go > away. For an AM that computes the expensive distance in amgettuple, > expect your 1/3 to 2/3 depending on recheck, not 97%. I've posted the > script and raw CSV if anyone wants to rerun them in a gist. > > https://gist.github.com/gburd/3f6acd95583845f4924a548167a58e07 > > In hindsight that was not a fair statistic to quote, the measure should > have been limited to the impact of the change. Benchmarking is hard, > and this time I wasn't trying to cheat, but I guess that 97% shouldn't > have been the lead or it should have been made clear what I'd measured.
No, I do think that it is very important to keep this in mind, too: We don't just avoid evaluating the possibly expensive distance up to 2 more times per row than necessary, but also avoid re-projecting the index column definition itself (which clearly can be extremely costly). That column projection is something I'd mostly overlooked when trying to figure out the performance difference: Whilst detoasting is a part in that re-projection cost, it is not necessarily the most expensive part of re-evaluating the full order-by expression. Kind regards, Matthias van de Meent Databricks (https://www.databricks.com)
