Beyond Performance: Do Indexes Offer Advantages Over Filter Functions?

I've observed that a query like the following:

export const getUserByNameAndEmail = query({
  args: {
    email: v.string(),
    name: v.string(),
  },
  handler: async (ctx, { email, name }) => {
    return await ctx.db
      .query("users")
      .filter((q) =>
        q.and(q.eq(q.field("name"), name), q.eq(q.field("email"), email))
      )
      .unique();
  },
});

export const getUserByNameAndEmail = query({
  args: {
    email: v.string(),
    name: v.string(),
  },
  handler: async (ctx, { email, name }) => {
    return await ctx.db
      .query("users")
      .filter((q) =>
        q.and(q.eq(q.field("name"), name), q.eq(q.field("email"), email))
      )
      .unique();
  },
});

works perfectly fine without needing to create any indexes(index || withIndex). Aside from the performance boost that indexes provide, are there any other potential issues we might encounter by relying solely on the filter function? Could there be situations where using an index is necessary to ensure the query functions correctly?

2 Replies

sshader•4mo ago

In terms of correctness, using a filter and using an index should behave the same. Queries using indexes will be faster (O(log n) vs. O(n)), will be less likely to hit limits, and will need to recompute less. Using your example of finding a user by name and email, when using a filter, we're scanning over every entry in the "users" table and checking if the name and email match. This is fine if your users table is pretty small, but if it gets bigger, you can hit the "number of documents scanned" limit (https://docs.convex.dev/production/state/limits#functions). Additionally, this query will reactively recompute any time any entry in the users table changes (not just the one entry you returned). So that's an extra function call + the database bandwidth for loading the entire users table. If we used an index instead (let's say an index on email assuming there aren't many users with the exact same email), we're using an index to narrow down to the chunk of the "users" table that matches the email. Only the entries matching the email count towards the number of documents scanned. Additionally, the query will reactively recompute only when one of the documents with the matching email changes (or when a document is added / changed so that it matches that email), as opposed to recomputing on every change to the table. So the tl;dr is that yeah, indexes are for performance not correctness, but performance also includes staying within limits as well as lower database bandwidth + less unnecessary function recomputation (which means a cheaper app to run). (https://stack.convex.dev/databases-are-spreadsheets is how I like to think about databases and how indexes work)

Databases are Spreadsheets

I want to share my mental model of databases: - Databases are just big spreadsheets - An index is just a view of the spreadsheet sorted by one or mor...

Limits | Convex Developer Hub

We’d love for you to have unlimited joy building on Convex but engineering

DeepakDahiyaOP•4mo ago

Got it, thank you very much!

Beyond Performance: Do Indexes Offer Advantages Over Filter Functions?

Did you find this page helpful?