SQL DB中动态排序查询性能优化的设计模式

Question

我有一个具有相当大的活动数据集(比如cars)的应用程序，它拥有大约200万行活动数据。每辆“车”都有多种属性(列)，如价格、里程、年份、品牌、型号、燃料类型等。

现在，在我的web应用程序中的每一辆车的/show页面上，我需要列出前十名最“相似”的汽车。正如我从来不知道的那样，如果一辆汽车是一种非常普通或非常罕见的汽车(在实际执行db查询之前)，我设计了一种模式，在“同类-cars”-query中几乎不进行任何过滤(-clauses)。相反，我做了大量的ORDER BY-clauses，结合基于当前汽车视图数据的CASE WHEN-statements。假设一个用户在看一辆Ford Focus, 2010, 30.000km, Gasoline, 12490EUR from around Düsseldorf汽车。然后我会做这样的事情：

SELECT "cars".*
  FROM de."cars" 
  WHERE ("cars"."id" != 24352543) 
    AND "cars"."sales_state" = 'onsale' 
    AND (cars.is_disabled IS NOT TRUE) 
    ORDER BY
      CASE WHEN ABS(cars.price - 12490) < cars.price * 0.2 THEN 1 WHEN ABS(cars.price - 12490) < cars.price * 0.4 THEN 2 WHEN ABS(cars.price - 12490) < cars.price * 0.6 THEN 3 ELSE 4 END, 
      CASE WHEN fuel_type = 'Gasoline' THEN 0 ELSE 1 END, 
      ABS(cars.price - 12490), 
      CASE WHEN ST_Distance( ST_GeographyFromText( 'SRID=4326;POINT(' || cars.longitude || ' ' || cars.latitude || ')' ), ST_GeographyFromText('SRID=4326;POINT(12.172130 48.162990)') ) <= 30000 THEN 1 WHEN ST_Distance( ST_GeographyFromText( 'SRID=4326;POINT(' || cars.longitude || ' ' || cars.latitude || ')' ), ST_GeographyFromText('SRID=4326;POINT(12.172130 48.162990)') ) <= 100000 THEN 2 ELSE 3 END, 
      ABS(cars.year - 2010), 
      ABS(cars.km - 30000)
    LIMIT 10

实际上，还有更多的订购条款。

现在这很方便，因为无论找到10辆与当前汽车相似的“相关”汽车多么“容易”，查询总是会返回一些- -问题是--很慢，而且几乎不可能根据我的知识进行索引。在200万张唱片上这样做，即使我有一个超快的超快专用PostgreSQL 11,300‘t内存，10 SSD 1032核心服务器，这仍然需要我大约2-4秒，时间我没有。我需要它降到<200毫秒。

我一直在绞尽脑汁地寻找解决这个问题的方法，但由于我对大规模解决这样的问题缺乏经验，我不确定哪种方法会更好，解决挑战。我的一些想法是：

• 以迭代的方式执行查询，其中我在某些列上筛选(WHERE)的术语(例如，从限制价格子集上的数据开始)，以减少数据集。然后，如果返回结果，很好，否则执行另一个稍宽的查询，以此类推。
• 使用一种完全不同的算法，可能会预先填充汽车的某些相似性度量列。
• 利用一些内部的PostgreSQL特性/扩展可以加快事情的速度，不管这些是什么？

Answer 1

对于可能的sql复杂性和转移(许多不同的模式)，以及您提到的时间(250 ms)，我应该强制sql遵循尽可能简单和有效的“计划”，方法是一次分解一个过滤器。

我在一个循环中工作(每次)随机的滤波器集，从过滤器中我判断出更重要的是，选择PKs，然后在其他循环中加入Pks。

这样你就有机会在所有随机滤波器集中获得最佳时间，而且你可能很快就知道0的结果。

更多细节--例如:首先，你把注意力集中在你搜索的项目上，我相信这就是car.id。因此，在随机过滤器上需要一组Car.id值。假设你有20个可能的过滤器。每个过滤器导致一组car.id值。有些过滤器可能直接在car.id所在的表上工作。其他一些可能需要连接到1-2或3个表。然而，所有过滤器一起可能需要10-15连接。最少有几张桌子加入进来，就越有机会得到一个好的计划。

假设你有3个过滤器，过滤器2，7和14。连接例如12个表格和过滤器与这3个过滤器可能是有效的，也可能是无效的。如果是的话，另一个组合就不会了。所以我提议的是(伪码)：

procedure/table function get carids as
for each optional filter 1 to 20
 if filter is set
  select car.id from car (possible joins) where filter=filter.value and car.id 
  in (previous car.id found)
  if count(car.id)=0 end and return no results
 end if
end for
return car.id collected

还可以指定筛选器处理的顺序。如果您知道从一组5-6筛选器中，至少有一个在99%的搜索中使用，那么首先对它们进行排序将导致将car.id值缩小到0-很少的范围，在前5选择最大值。

Answer 2

您不可能得到那么快，因为您必须对所有查询结果执行顶级N排序，这将是缓慢的，即使您曲柄work_mem。

ORDER BY子句现在是不可索引的。

如果您在查询方面更灵活一些，也许您可以尝试这样的方法：

第一个查询：

WITH priced_cars AS (
  SELECT SELECT cars.*
    FROM de.cars
    WHERE (cars.id != 24352543)
      AND cars.sales_state = 'onsale'
      AND (cars.is_disabled IS NOT TRUE)
      AND cars.price BETWEEN 12490*5/6 AND 12490*5/4
)
SELECT * FROM priced_cars
ORDER BY
  CASE WHEN fuel_type = 'Gasoline' THEN 0 ELSE 1 END, 
  ABS(price - 12490), 
  CASE
    WHEN ST_Distance( ST_GeographyFromText( 'SRID=4326;POINT(' || longitude || ' ' || latitude || ')' ), ST_GeographyFromText('SRID=4326;POINT(12.172130 48.162990)') ) <= 30000
    THEN 1
    WHEN ST_Distance( ST_GeographyFromText( 'SRID=4326;POINT(' || longitude || ' ' || latitude || ')' ), ST_GeographyFromText('SRID=4326;POINT(12.172130 48.162990)') ) <= 100000
    THEN 2
    ELSE 3
  END, 
  ABS(year - 2010), 
  ABS(km - 30000)
LIMIT 10;

此查询可以使用如下索引：

CREATE INDEX ON de.cars (price)
  WHERE sales_state = 'onsale' AND is_disabled IS NOT TRUE;

这将只对应于您的第一个ORDER BY列为1的cars，但是它可以很快，因为它可以使用索引。

如果你在那边找到10辆车，你就完蛋了。

否则，运行第二个带有WHERE条件的price查询，该条件与price上的第二个最佳条件相对应，后者同样可以使用相同的索引，但速度要慢一些。

这样做，直到您有10辆汽车(最后一次查询将没有条件的price和将是像以前一样慢)。

如果必须运行四个这样的查询，这将是一个损失，因为在前三个查询中找不到10辆汽车，但在另一种情况下可能更快。