Как эффективно написать запрос DISTINCT в Django с таблицей, имеющей внешние ключи

Я хочу показать разные города пользователей в раскрывающемся списке переднего плана. Для этого я делаю запрос к базе данных, который извлекает отдельные city_name из таблицы City, но только те города, в которых присутствуют пользователи.

Нечто подобное ниже работает для небольшого размера таблицы User, но занимает очень много времени, если таблица User имеет размер 10 миллионов. Однако отдельных городов этих пользователей по-прежнему ~ 100.

class City(models.Model):
    city_code = models.IntegerField(unique=True)
    city_name = models.CharField(max_length=256)

class User(models.Model):
    city = models.ForeignKey('City', to_field='city_code')

Теперь я пытаюсь найти разные названия городов как:

City.objects.filter().values_list('city__city_name').distinct()

что означает это в PostgreSQL:

SELECT DISTINCT "city"."city_name" 
FROM "user" 
LEFT OUTER JOIN "city" 
                ON ("user"."city_id" = "city"."city_code");

Время: 9760,302 мс

Это ясно показало, что PostgreSQL не использует индекс для 'user'.'city_id'. Я также читал об обходном решении здесь, которое включало запись пользовательский SQL-запрос, который каким-то образом использует index.

Я попытался найти отдельный 'user'.'city_id', используя приведенный выше запрос, и на самом деле это оказалось довольно быстро.

WITH 
    RECURSIVE t(n) AS 
                     (SELECT min(city_id) 
                      FROM user 
                      UNION 
                      SELECT 
                            (SELECT city_id 
                             FROM user 
                             WHERE city_id > n order by city_id limit 1) 
                      FROM t 
                      WHERE n is not null) 
                      SELECT n 
                      FROM t;

Время: 79,056 мс

Но теперь мне трудно включить это в мой код Django. Я все еще думаю, что это своего рода хак, добавляющий собственный запрос в код для этого. Но меня больше беспокоит то, что имя столбца может быть полностью динамическим, и я не могу жестко закодировать эти имена столбцов (например, city_id и т. д.) в коде.

#original_fields could be a list from input, like ['area_code__district_code__name']
dataset_klass.objects.filter().values_list(*original_fields).distinct()

Для использования пользовательского запроса потребуется как минимум разделить имя поля с «__» в качестве разделителя и обработать первую часть. Но мне это кажется плохим взломом.

Как я могу улучшить это?

PS. Пример City User показан только для пояснения сценария. Синтаксис может быть неправильным.


django sql django-models django-postgresql
person prat0318    schedule 16.11.2013    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Наконец-то я добрался до этого обходного решения.

from django.db import connection, transaction

original_field = 'city__city_name'
dataset_name = 'user'
dataset_klass = eval(camelize(dataset_name))

split_arr = original_field.split("__",1)
"""If a foreign key relation is present
"""
if len(split_arr) > 1:
    parent_field = dataset_klass._meta.get_field_by_name(split_arr[0])[0]
    cursor = connection.cursor()
    """This query will run fast only if parent_field is indexed (city_id)
    """
    cursor.execute('WITH RECURSIVE t(n) AS ( select min({0}) from {1} '
                   'union select (select {0} from {1} where {0} > n'
                   ' order by {0} limit 1) from t where n is not null) '
                   'select n from t;'.format(parent_field.get_attname_column()[1], dataset_name))
    """Create a list of all distinct city_id's"""
    distinct_values = [single[0] for single in cursor.fetchall()]
    """create a dict of foreign key field to the above list"""
    """to get the actual city_name's using _meta information"""
    filter_dict = {parent_field.rel.field_name+'__in':distinct_values}
    values = parent_field.rel.to.objects.filter(**filter_dict).values_list(split_arr[1])
else:
    values = dataset_klass.objects.filter().values_list(original_field).distinct()

Который использует индекс city_id в таблице user, работает довольно быстро.

person prat0318    schedule 17.11.2013