在django中是不同寻常的昂贵的查询？ [英] is distinct an expensive query in django?

问题描述

我有三种型号：产品，类别和地点。
产品与Category和Place有ManyToMany关系。
我需要得到一个类别列表，至少在产品匹配特定的地方。
例如我可能需要获得波士顿至少有一个产品的所有类别。

我有100个类别，500个地方和100,000个产品。 / p>

在具有10K产品的sqlite中，查询需要一秒钟。
在生产中我将使用postgresql。

我正在使用：

  categories = Category.objects.distinct（）。filter（product__place__name =Boston）
  

这个查询是否昂贵？
有更好的方法吗？

这是connection.queries的结果

  {'time'：'0.929'，'sql'：u'SELECT DISTINCTcatalog_category。id，catalog_categorynameFROMcatalog_categoryINNER JOINcatalog_product_categories ON（catalog_categoryid_id =catalog_product_categoriescategory_id）INNER JOINcatalog_productON（catalog_product_categoriesproduct_id=catalog_productid）INNER JOINcatalog_product_places id=catalog_product_placesproduct_id）INNER JOINcatalog_placeON（catalog_product_places。car_id=catalog_carid）WHEREcatalog_place。name= Boston ORDER BY catalog_category。nameASC'}] 
  

谢谢

解决方案

这不仅仅是一个Django问题;大多数SQL实现中的DISTINCT缓慢，因为它是一个比较难的操作。 这里是一个很好的讨论，为什么在Postgres特别缓慢。

处理这个的一种方法是使用Django的出色的缓存机制在这个查询中，假设结果不会经常变化，而小的陈旧不是问题。另一种方法是单独列出不同类别的列表，也许在另一个表中。

I have three models: Product, Category and Place. Product has ManyToMany relation with Category and Place. I need to get a list of categories with at least on product matching a specific place. For example I might need to get all the categories that has at least one product from Boston.

I have 100 categories, 500 places and 100,000 products.

In sqlite with 10K products the query takes ~ a second. In production I'll use postgresql.

I'm using:

categories = Category.objects.distinct().filter(product__place__name="Boston")

Is this query going to be expensive? Is there a better way to do this?

This is the result of connection.queries

{'time': '0.929', 'sql': u'SELECT DISTINCT "catalog_category"."id", "catalog_category"."name" FROM "catalog_category" INNER JOIN "catalog_product_categories" ON ("catalog_category"."id" = "catalog_product_categories"."category_id") INNER JOIN "catalog_product" ON ("catalog_product_categories"."product_id" = "catalog_product"."id") INNER JOIN "catalog_product_places" ON ("catalog_product"."id" = "catalog_product_places"."product_id") INNER JOIN "catalog_place" ON ("catalog_product_places"."car_id" = "catalog_car"."id") WHERE "catalog_place"."name" = Boston  ORDER BY "catalog_category"."name" ASC'}]

Thanks

解决方案

This is not just a Django issue; DISTINCT is slow on most SQL implementations because it's a relatively hard operation. Here is a good discussion of why it's slow in Postgres specifically.

One way to handle this would be to use Django's excellent caching mechanism on this query, assuming that the results don't change often and minor staleness isn't a problem. Another approach would be to keep a separate list of just the distinct categories, perhaps in another table.

这篇关于在django中是不同寻常的昂贵的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！