SQlAlchemy. Как оптимизировать запросы?

Question

[Павел]

Здравствуйте.

Не получается сократить время запроса.
Есть 3 таблицы. Контекст, запрос, точка. В контексте может быть много запросов, а в запросе может быть много точек. Самая объёмная таблица - точка.
Требуется для каждого контекста найти сумму точек. Т.е. для каждого контекста перебрать его запросы и подсчитать количество точек, принадлежащих этим запросам, и сложить.

Как дело обстоит сейчас. Ищутся все контексты и выводятся в таблицу (html), для столбца суммы точек для каждого контекста отдельно создаётся свой запрос по поиску суммы точек, что плохо сказывается на скорости. С увеличением количества точек общее время запрос (и отображения страницы) сильно увеличивается.

# Model
class Context(Model):

    requests = relationship(Request, backref="context")

class Request(Model):

    context_id = db.Column(db.ForeignKey('context.id'))
    points = relationship('Point', backref='requests', lazy='dynamic')

class Point(Model):

    request_id = db.Column(db.ForeignKey('requests.id'))

class Context(Model):

	def get_point_count(self):
	    subquery = Request.query \
	        .with_entities(Request.id) \
	        .with_parent(self) \
	        .subquery()

	    count = Point.query \
	        .join(Request, Point.requests) \
	        .filter(Request.id.in_(subquery)) \
	        .distinct(Point.attr) \
	        .count()
	    return count

# View

query = Context.query
query = query.with_entities(func.max(Context.id))
query = query.group_by(Context.user_id)
subquery = query.subquery()

query = query.filter(Context.id.in_(subquery))
query = query.order_by(Context.id.desc())
contexts = query.all() # ~200ms

# в таблице отображаю контексты и для каждого контекста показываю количество точек
for ctx in contexts:
	ctx.get_point_count() # ~20ms

# Если контекстов на странице 20, то соответственно к основному запросу добавляется 20 запросов, что увеличивает общее время на ~400ms

Как более правильно решить эту задачу?

Спасибо.

Answer 1

[Егор Стаховский]

Посоветую вам начать с включения SQLALCHEMY_ECHO и просмотра плана результирующего запроса.
Как быстрый совет - ограничивайте запрос данными, которые вам нужны, и не бойтесь подзапросов. В большей части случаев бэкенд БД сделает всю оптимизацию за вас.

result = Context.query.join(
    Request, Request.context_id == Context.id_
).join(
    Point, Point.request_id == Request.id_
).group_by(Context.id_).with_entities(
    Context.field1, ...
    func.count(Point.id_).label('points_count')
)

Comments

[Павел]

Как можно добавить в такой запрос дополнительные условия? Модели Request и Point имеют дополнительные атрибут visible.

Т.е. нужно подсчитать количество Point при Request.visible == True и Point.visible == True
и второе количество без условий. Нужно джйонить по количеству условий?

[Егор Стаховский]

Павел, используйте sqlalchemy.sql.expression.case.
В with_entitites добавьте кроме func.count(Point.id_).label('points_count') ещё что-то вроде:
func.count(case([and_(Request.visible == True, Point.visible == True), 1], else_=literal_column("NULL"))).label('visible_points_count').
Документация, примеры использования в ней есть.

[Павел]

Егор Стаховский, спасибо!

[Павел]

Егор Стаховский, я упустил один важный момент. Point имеет поле attr, по которому не должно быть совпадений.

Как в

func.count(case([and_(Request.visible == True, Point.visible == True), 1], else_=literal_column("NULL"))).label('visible_points_count')

можно добавить distinct по Point.attr?

[Егор Стаховский]

Павел, могут ли быть у разных Request и Context объекты Point с одинаковым attr?
Какая в этом случае будет логика?

result = Context.query.join(
    Request, Request.context_id == Context.id_
).join(
    Point, Point.request_id == Request.id_
).disctinct(
    Context.id_,
    Request.id_,
    Point.attr
).group_by(Context.id_).with_entities(
    Context.field1, ...
    func.count(Point.id_).label('points_count'),
    func.count(case([and_(Request.visible == True, Point.visible == True), 1], 
                    else_=literal_column("NULL"))).label('visible_points_count')
)

[Павел]

Егор Стаховский, у разных Request могут быть Point с одинаковыми attr. Если эти аттрибуты совпали, то мне нужно считать их как один Point.

result = Context.query.join(
    Request, Request.context_id == Context.id_
).join(
    Point, Point.request_id == Request.id_
).disctinct(
    Point.attr
).group_by(Context.id_).with_entities(
    Context,
    func.count(Point.id_).label('points_count'),
    func.count(case([and_(Request.visible == True, Point.visible == True), 1], 
                    else_=literal_column("NULL"))).label('visible_points_count')
)

Так оно просит добавить Point.attr в GROUP BY

[Егор Стаховский]

Логика довольно странная. Если у вас будет 2 context и 2 request с 2-мя одинаковыми point'ами - итоговое количество для одного из context'ов будет 0. Так действительно должно быть?
По решению - попробуйте сделать subquery для Point. Если на attr есть индекс, на производительность это не сильно повлияет.

points = Point.query.disctinct(Point.attr).with_entities(Point.id_.label('point_id'), Point.request_id.label('point_request_id'), Point.visible.lable('point_visible'))

И join'ить, соответственно, этот query вместо Point'а.

[Павел]

Егор Стаховский, не совсем так: уникальность Point.attr должна быть в пределах одного Context.