+1 as well. I think this is a fine solution.
On Thu, Sep 3, 2026 at 9:56 PM Mike Carey <[email protected]> wrote: > > +1 to this. Also, some market research showed that neither MySQL or > Postgres have such an EXCLUDE option, so it is not something that folks > will be surprised not to have. In the future one option would be to > support partial indexes as a way to exclude or include objects. > > Cheers, > Mike > > On Thu, Sep 3, 2026 at 7:28 PM Ali Alsuliman <[email protected]> > wrote: > > > We have discussed the issue and decided to go with the following: > > > > 1. We don’t talk about EXCLUDE UNKNOWN KEY or INCLUDE UNKNOWN KEY. > > 2. We try to index the documents with “valid” embedding and exclude the > > ones with “bad” embedding like we are doing today. We document that > > this is > > the current behaviour (given it’s not the typical case). > > 3. Meanwhile, think about how to solve it from a user model perspective > > and have the infrastructure ready for that. > > > > So we need to exclude EXCLUDE (delete that from the grammar as part of > > creating a VTREE index) and then live with the difference in results (and > > document it as a quirk). > > > > On Wed, Aug 26, 2026 at 2:32 PM Mike Carey <[email protected]> wrote: > > > > > Great discussion (and issue) summary, Ian...! To have "perfect" SQL++ > > > semantics, the following query actually cannot be run as an index-only > > > plan: > > > > > > LET qvec = [1.1,2.2,3.3,4.4] > > > SELECT id > > > FROM col > > > WHERE year > 2000 > > > ORDER BY ann_distance(qvec, embedding) LIMIT 20; > > > > > > However, the following query (Q2) could indeed be run index-only: > > > > > > LET qvec = [1.1,2.2,3.3,4.4] > > > SELECT id > > > FROM col > > > WHERE year > 2000 > > > AND ann_distance(qvec, embedding) IS NOT NULL > > > ORDER BY ann_distance(qvec, embedding) LIMIT 20; > > > > > > The difference is that IS NOT UNKNOWN says that it's okay that the index > > > is partial with respect to bad embeddings. Semantically, > > > ann_distance(...) is just a function and it will return NULL if qvec and > > > col.embedding aren't comparable, and ORDER BY is not unhappy ordering > > > NULLs as well as non-null distances. > > > > > > The challenge is that most users probably want to write Q1 for > > > simplicity yet they really want Q2's semantics. Moreover, in normal > > > cases there will probably be no "crap embeddings", so the two queries > > > will yield the same results in such cases (modulo ann vs. knn) - so this > > > issue is really a corner case, but an important one. > > > > > > One minor simplification might be to offer users the ability to say more > > > at collection creation time. If users could tell us that col.embedding > > > is an embedding field during CREATE COLLECTION, and could say that the > > > field is an array of 4 doubles, and that non-compliant incoming objects > > > should be rejected as erroneous, then we would know that col.embedding > > > is NOT NULL in the collection, and that if vec is also legit, the two > > > queries will be the same semantically because ann_distance won't return > > > NULL. > > > > > > A challenge that Ali pointed out in the discussion is that we can't > > > always control what the user puts into their collections - so.... > > > > > > I know this isn't an answer, but I'm hoping it will spur more thinking > > > and ideas here. > > > > > > Cheers, > > > Mike > > > > > > > > > On 8/26/26 9:43 AM, Ian Maxon wrote: > > > > Hello fellow devs, > > > > There was an interesting question posed by Ali in a conversation > > > > between some committers and contributors that I wanted to bring to the > > > > larger community for discussion. I think it has some interesting > > > > design considerations, for all kinds of specialized indexes. I will > > > > try to describe it as I remember it today and Ali can certainly > > > > correct me if I am misremembering. > > > > > > > > Consider a query like the following, without an index: > > > > ``` > > > > LET qvec = [1.1,2.2,3.3,4.4] > > > > SELECT id > > > > FROM col > > > > WHERE year > 2000 > > > > ORDER BY ann_distance(qvec, embedding) LIMIT 20; > > > > ``` > > > > > > > > This query today might return something like this (falling back to > > KNN): > > > > ``` > > > > {"id": 1, "year": 2020, "embedding": 1} > > > > {"id": 2, "year": 2020, "embedding": [2.1, 1.2, 3.4, 5.6]} > > > > ``` > > > > > > > > The thing to note here is that an embedding that isn't in the same > > > > dimension gets returned in the ORDER clause, because KNN scans the > > > > whole dataset, null is comparable, and we return null when the two > > > > vectors being compared aren't of the same dimension. > > > > > > > > Now consider what happens if you create a vector index like so on > > 'col': > > > > ``` > > > > CREATE INDEX idx > > > > ON col(embedding VECTOR) > > > > INCLUDE (year) > > > > TYPE VTREE > > > > WITH { "dimension": 4 } > > > > EXCLUDE UNKNOWN KEY; > > > > ``` > > > > The query now might return something like this: > > > > ``` > > > > {"id": 2, "year": 2020, "embedding": [2.1, 1.2, 3.4, 5.6]} > > > > ``` > > > > > > > > Because this query can be answered with an index-only plan, and the > > > > VTree doesn't include records which end up having unknown or null > > > > distance, like those without the same dimension. > > > > > > > > The main rub here is that there has been a general design point in > > > > AsterixDB (and in other DBMS), that the presence or absence of an > > > > index can't change the result of a query. The index should simply > > > > (hopefully) accelerate the query, not change its semantics. This means > > > > for an index-only plan, the index has to know about every record that > > > > also exists in the primary. > > > > > > > > Index-only plans for any other type that is supported by the > > > > Heterogeneous index (which is a BTree) don't fall into this; it builds > > > > a complete index. In fact this was one of the many advantages of this > > > > index type, compared to older typed indexes, and enabled the use of > > > > index-only plans without any schema information. > > > > > > > > However this advantage can't be used for these kinds of special > > > > indexes which can't index all types of data. They can only be complete > > > > with respect to types within their domain. We have many indexes of > > > > this type today: RTree, Fulltext/inverted, and VTree. Furthermore in > > > > the case of VTree, not using index-only plans induce a particularly > > > > severe performance penalty, so simply falling back to the primary > > > > isn't ideal. > > > > > > > > With all that, we now arrive at the main dilemma: Indexes, even > > > > index-only plans, shouldn't change query results. However making a > > > > VTree either include null keys, or not use an index-only plan for Top > > > > K queries, will give a major performance hit. > > > > > > > > My personal opinion is that we should, in this particular case, simply > > > > make it abundantly clear that for approximate indexes, they are not > > > > complete. For RTree it is a harder case because it is an exact index, > > > > but I think it's similar that data which doesn't exist in the space > > > > the RTree is over, it doesn't make much sense to return something from > > > > a spatial predicate on it. Overall I think that these special indexes > > > > are intended for special use cases, and that it is OK to bend some > > > > design philosophies a little to accommodate them. > > > > > > > > What does everyone else think? I sort of feel like there should be > > > > some third way to break this dilemma, but I can't put my finger on it. > > > > > > > > - Ian > > > > > > > > > > -- > > Regards, > >
