Correct way to use indexes?

[mellow dune]

I have a model with the fields word and user, which I use together in a lot of queries. I made this manager function to test how fast the get() function is for these two fields:

    def speedtest(self):
        start = timeit.default_timer()
        for obj in self.all().iterator():
            self.get(user=obj.user, word=obj.word)
        print(timeit.default_timer() - start)

Without indexing it takes around 13 seconds to get ~18,000 items. I then add indexes to the model like so:

class Meta:
  indexes = [models.Index(fields = ["user", "word"])]

Now, it takes about 11 seconds. A 15% increase isn't bad, but to be honest, I was expecting something more significant This is my first time using SQL indexing, so I wanted to ensure that I'm doing the right thing.

[glacial pond]

I'm not sure the issue is with your indexes, in this case. If you call get() 18k times, do expect it to be very slow. I'm sure there is a way to optimize this using some funky DB features like row constructors, though I neither have the data nor the time to figure it out right now. But I shall leave you some links to research:

• https://dba.stackexchange.com/a/191932
• https://stackoverflow.com/a/69323794
• https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
• https://docs.djangoproject.com/en/4.2/ref/models/expressions/#func-expressions
  This works:

>>> ab_func = Func(F("first_name"), F("last_name"), function="ROW", output_type=TextField())
>>> for u in User.objects.annotate(ab=ab_func): print(u, u.ab)

I also haven't managed to use it with in_bulk yet though.

[winged sandal]

just to add, if you're pulling every row from a table, indexing won't help you. Indexes only help you when you're filtering (and sometimes ordering) data