SQL - какой индекс ускорит запрос COUNT ()

#python #sql #django #postgresql #indexing

#python #sql #django #postgresql #индексирование

Вопрос:

У меня есть очень простая база данных Postgres, которая выглядит так (она определена в Django, но это не имеет значения):

 class Country:
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    # Not important
    # name = models.TextField(nullable=False)

class City:
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    # name = models.TextField(nullable=False)
    country = models.ForeignKey(Country)

class Street:
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    # name = models.TextField(nullable=False)
    city = models.ForeignKey(City)
    # Just some boolean field
    is_big = models.BooleanField(nullable=False)

Я хочу запросить количество «больших» улиц в стране. Это делается так:

 SELECT COUNT(*)
  FROM Street
 INNER JOIN City
    ON Street.city_id = City.id
 WHERE Street.is_big = true AND City.country_id = 'xxxxxxx'::uuid

Всего в базе данных около ~ 20 стран, ~ 5000 городов и ~ 2 миллионов улиц, что немного, но этот запрос может занять иногда 1-2 секунды.

Какой индекс я должен добавить, чтобы ускорить этот запрос?

1. Проиндексируйте внешний ключ и все поля из WHERE предложения.

2. Как обычно, вам нужно проанализировать, ОБЪЯСНИТЬ, ПРОАНАЛИЗИРОВАТЬ вывод вашего запроса, чтобы найти, что замедляет его в вашем конкретном случае. Пример из понимания ОБЪЯСНЯЕТ «20 Поможет ли здесь индекс?», Где запрос с предложением WHERE » должен прочитать 99,95% таблицы. В этом случае он никогда не будет использовать индекс »

Ответ №1:

Это ваш запрос:

 SELECT COUNT(*)
FROM Street s JOIN
     City c
     ON s.city_id = c.id
WHERE s.is_big = true AND c.country_id = 'xxxxxxx'::uuid;

Для этого есть два возможных подхода. Однако я подозреваю, что ни один из них не будет работать действительно хорошо во всех случаях — я предполагаю, что в некоторых странах много больших улиц.

Первый подход заключается в фильтрации по большим улицам: street(is_big, city_id) и city(id, country_id) . Второй — фильтровать по конкретной стране: city(country_id, id) и street(city_id, is_big) .

Если база данных статична, то запрос может использовать сканирование только по индексу — при условии, что данные «видны».