Django: Random query slow - despite optimizations

Question

I have an build an API that is supposed to return 10 randomly selected results from a - large queryset.

I have the following 4 models:

class ScrapingOperation(models.Model):
    completed = models.BooleanField(default=False)
    (...)

    indexes = [
        models.Index(fields=['completed'], name='completed_idx'),
        models.Index(fields=['trusted'], name='trusted_idx'),
    ]

    @property
    def ads(self):
        """returns all ads linked to the searches of this operation"""
        return Ad.objects.filter(searches__in=self.searches.all())


class Search(models.Model):
    completed = models.BooleanField(default=False)
    scraping_operation = models.ForeignKey(
            ScrapingOperation,
            on_delete=models.CASCADE,
            related_name='searches'
    )
    (...)


class Ad(models.Model):
    searches = models.ManyToManyField('scraper.Search', related_name='ads')
    (...)


class Label(models.Model):
     value = models.Integerfield()
     linked_ad = models.OneToOneField(
         Ad, on_delete=models.CASCADE, related_name='labels'
     )

The database currently has 400.000 + Ad objects in it, but the average ScrapingOperation has 14000 Ad objects linked to it. I want the API to return 10 random results from these +/- 14000 that do not yet have a linked Label object (Of which only up to a few hundred exist per operation)

So the 10 random results have to be returned from a query that contains 14.000 objects.

An earlier version had to return only 1 result, but used the much slower sort_by('?') method. When I had to scale it up to return random 10 Ad objects I used a new approach based partly on this stackoverflow answer

Here is the code that selects (and returns) the 10 random objects:

# Get all ads linked to the last completed operation
last_op_ads = ScrapingOperation.objects.filter(completed=True).last().ads

# Get all ads that don't have an label yet
random_ads = last_op_ads.filter(labels__isnull=True)

# Get list ids of all potential ads
id_list = random_ads.values_list('id', flat=True)
id_list = list(id_list)

# Select a random sample of 10, get objects with PK matches
samples = rd.sample(id_list, min(len(id_list), 10))
selected_samples = random_ads.filter(id__in=samples)

return selected_samples

However, despite my optimizations this query takes 10+ seconds to complete, creating a very slow API.

Is this long delay just inherent to random queries? (And if so, how do other programmers deal with this limitation?) or is there an error / inefficiency in my code that I am missing?

Edit: Based on the response I've included the raw sql query below (Note: these where run on my local environment, which contains only 5% of the data that my production environment contains)

{'sql': 'SELECT "scraper_scrapingoperation"."id", 
"scraper_scrapingoperation"."date_started", 
"scraper_scrapingoperation"."date_completed",
"scraper_scrapingoperation"."completed", 
"scraper_scrapingoperation"."round", 
"scraper_scrapingoperation"."trusted" FROM "scraper_scrapingoperation" 
WHERE "scraper_scrapingoperation"."completed" = true ORDER BY 
"scraper_scrapingoperation"."id" DESC LIMIT 1', 'time': '0.001'}


{'sql': 'SELECT "database_ad"."id" FROM "database_ad" INNER JOIN 
"database_ad_searches" ON ("database_ad"."id" = 
"database_ad_searches"."ad_id") LEFT OUTER JOIN "classifier_label" ON
("database_ad"."id" = "classifier_label"."ad_id") WHERE 
("database_ad_searches"."search_id" IN (SELECT U0."id" FROM 
"scraper_search" U0 WHERE U0."scraping_operation_id" = 6) AND 
"classifier_label"."id" IS NULL)', 'time': '1.677'}

Edit 2: I tried an alternative approach, with deeper select_related parameters

        random_ads = ScrapingOperation.objects.prefetch_related(
            'searches__ads__labels',
        ).filter(completed=True).last().ads.exclude(
            labels__isnull=True
        )

        id_list = random_ads.values_list('id', flat=True)
        id_list = list(id_list)

        samples = rd.sample(id_list, min(
            len(id_list), 10))

        selected_samples = random_ads.filter(
            id__in=samples)

        return selected_samples

which produces the following SQL queries:

{'time': '0.008', 'sql': 'SELECT "scraper_search"."id", 
"scraper_search"."item_id", "scraper_search"."date_started", 
"scraper_search"."date_completed", "scraper_search"."completed", 
"scraper_search"."round", "scraper_search"."scraping_operation_id", 
"scraper_search"."trusted" FROM "scraper_search" WHERE 
"scraper_search"."scraping_operation_id" IN (6)'}


 {'time': '0.113', 'sql': 'SELECT ("database_ad_searches"."search_id")
 AS "_prefetch_related_val_search_id", "database_ad"."id", 
 "database_ad"."item_id", "database_ad"."item_state", 
 "database_ad"."title", "database_ad"."seller_id", 
 "database_ad"."url", "database_ad"."price", 
 "database_ad"."transaction_type", "database_ad"."transaction_method",
 "database_ad"."first_seen", "database_ad"."last_seen", 
 "database_ad"."promoted" FROM "database_ad" INNER JOIN 
 "database_ad_searches" ON ("database_ad"."id" = 
 "database_ad_searches"."ad_id") WHERE 
 "database_ad_searches"."search_id" IN (130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160)'}


{'time': '0.041', 'sql': 'SELECT "classifier_label"."id", 
"classifier_label"."set_by_id", "classifier_label"."ad_id", 
"classifier_label"."date", "classifier_label"."phone_type", 
"classifier_label"."seller_type", "classifier_label"."sale_type" FROM
"classifier_label" WHERE "classifier_label"."ad_id" IN (1, 3, 6, 10, 20, 29, 30, 35, 43, (and MANY more of these numbers) ....'}



{'time': '1.498', 'sql': 'SELECT "database_ad"."id" FROM "database_ad"
INNER JOIN "database_ad_searches" ON ("database_ad"."id" = "database_ad_searches"."ad_id") LEFT OUTER JOIN "classifier_label" ON 
("database_ad"."id" = "classifier_label"."ad_id") WHERE 
("database_ad_searches"."search_id" IN (SELECT U0."id" FROM
"scraper_search" U0 WHERE U0."scraping_operation_id" = 6) AND NOT 
("classifier_label"."id" IS NOT NULL))'}

Each ScrapingOperation 'only' has +/- 14000 linked ads, but the total number of ads in production is 400.000 (and growing). All of the code above returns valid results on my local environment (which contains only 5% of the data) but returns 502 errors on the API in production.

Answer 1

I would attempt to isolate the linked ads first, then get 10 random out of them using order by a generated random column. I am not sure how this would be effective out of generated sql. To be sure, i would prefer to create a stored procedure exactly on the task since this is clear a data mining operation that ends up on random samples.