代码之家 › 专栏 › 技术社区 › Ernesto G

处理时间差异很大,取决于轻微的查询参数差异

activerecord postgresql ruby-on-rails

0

Ernesto G · 技术社区 · 8 年前

我有两个模型,partmaster和location,有一对多的关系。我必须使用这两个表的左联接来搜索字段part_masters.combo和locations.ubication。

我的问题是,相同的查询处理时间是完全不同的,这取决于查询的参数如何。

此查询的执行时间大约为 450毫秒 .

查询的计划是

plan for the query looking for 'P0'

SELECT DISTINCT "part_masters".* FROM "part_masters" LEFT OUTER JOIN 
"locations" ON "locations"."sap_cod" = "part_masters"."sap_cod" WHERE 
(unaccent(locations.ubicacion) ILIKE unaccent('%P0%')) AND 
(unaccent(part_masters.combo) ILIKE unaccent('%junta%')) AND 
(unaccent(part_masters.combo) ILIKE unaccent('%torica%')) ORDER BY 
"part_masters"."sap_cod" ASC

而另一个查询,只需将“p0”更改为“p0”作为位置的查询参数。 38秒 为了被处决,我大部分时间都超时了。

plan for the query looking for 'P01'

SELECT DISTINCT "part_masters".* FROM "part_masters" LEFT OUTER JOIN 
"locations" ON "locations"."sap_cod" = "part_masters"."sap_cod" WHERE 
(unaccent(locations.ubicacion) ILIKE unaccent('%P01%')) AND 
(unaccent(part_masters.combo) ILIKE unaccent('%junta%')) AND 
(unaccent(part_masters.combo) ILIKE unaccent('%torica%')) ORDER BY 
"part_masters"."sap_cod" ASC

分析输出:

Unique  (cost=3880.72..3880.77 rows=1 width=242) (actual 
time=39902.298..39902.305 rows=8 loops=1)
->  Sort  (cost=3880.72..3880.73 rows=1 width=242) (actual 
time=39902.297..39902.297 rows=8 loops=1)
    Sort Key: part_masters.sap_cod, part_masters.id, 
part_masters.descripcion_maestro, part_masters.ref_fabricante, 
part_masters.fabricante, part_masters.stock, part_masters.precio_medio, 
part_masters.planta_cod, part_masters.planta_nombre, 
part_masters.unidad_medida, part_masters.grupo_compras, 
part_masters.created_at, part_masters.updated_at, 
part_masters.combinada_maestro, part_masters.precio_estandar, 
part_masters.fabricante_nombre, part_masters.combo
    Sort Method: quicksort  Memory: 29kB
    ->  Nested Loop  (cost=0.00..3880.71 rows=1 width=242) (actual 
time=10393.015..39902.250 rows=8 loops=1)
          Join Filter: ((part_masters.sap_cod)::text = 
(locations.sap_cod)::text)
          Rows Removed by Join Filter: 438318
          ->  Seq Scan on part_masters  (cost=0.00..2451.75 rows=1 
 width=242) (actual time=2.135..315.211 rows=262 loops=1)
                Filter: ((unaccent(combo) ~~* unaccent('%junta%'::text)) AND 
 (unaccent(combo) ~~* unaccent('%torica%'::text)))
                Rows Removed by Filter: 38408
          ->  Seq Scan on locations  (cost=0.00..1409.24 rows=1578 width=5) 
 (actual time=0.107..148.671 rows=1673 loops=262)
                Filter: (unaccent((ubicacion)::text) ~~* 
 unaccent('%P01%'::text))
                Rows Removed by Filter: 37586
 Total runtime: 39902.358 ms

我把这个索引放在组合框中,没有被使用。

未使用组合框上的部分主控形状(Trigram索引)

没有任何locations.ubication的索引。

根据我链接的计划,实际的循环和共享的命中率都有很大的不同,我不知道这是否有帮助。

1 回复 | 直到 8 年前

1

Laurenz Albe 8 年前

您需要索引,以便PostgreSQL可以加快查询速度并计算更好的估计:

CREATE INDEX ON part_masters (lower(unaccent(combo)) text_pattern_ops);
CREATE INDEX ON locations (lower(unaccent(ubicacion)) text_pattern_ops);

然后运行 ANALYZE 在两张桌子上。

此外,您还必须重写以下三个条件:

unaccent(part_masters.combo) ILIKE unaccent('%junta%')

这样地:

lower(unaccent(part_masters.combo)) LIKE lower(unaccent('%junta%'))

这将使您获得显著的性能提升。