sql - 不同的查询，相同的结果(看起来)，完全不同的性能......为什么？

Question

目前我有两个不同的查询，它们返回完全相同的结果，但是，更改从中过滤结果的参数会使它们以非常不同的方式运行。

搜索 cartography 时的结果

查询 #1: 22 行/~860 毫秒；

SELECT eid FROM t_entidades 
WHERE  eid IN ( 
            SELECT     eid 
            FROM       t_entidades 
            WHERE      entidade_t LIKE '%cartography%'
)
OR     eid IN (
            SELECT    entidade as eid
            FROM      t_entidade_actividade ea
            LEFT JOIN t_actividades a ON a.aid = ea.actividade
            WHERE     a.actividade LIKE '%cartography%'
)

查询 #2: 22 行/~430 毫秒；

SELECT      eid FROM t_entidades WHERE entidade_t LIKE '%cartography%'
UNION
SELECT      entidade as eid
FROM        t_entidade_actividade ea
LEFT JOIN   t_actividades a ON a.aid = ea.actividade
WHERE       a.actividade LIKE '%cartography%'

搜索购物车时的结果

查询 #1: 715 行/~870 毫秒；

查询 #2: 715 行/~450 毫秒

搜索car时的结果

查询 #1: 从未等待足够长的时间......似乎需要永远，超过 1 就太多了

-- EXPLAIN OUTPUT:
"QUERY PLAN"
"Seq Scan on t_entidades  (cost=44997.40..219177315.47 rows=500127 width=4)"
"  Filter: ((SubPlan 1) OR (hashed SubPlan 2))"
"  SubPlan 1"
"    ->  Materialize  (cost=37712.46..38269.55 rows=40009 width=4)"
"          ->  Seq Scan on t_entidades  (cost=0.00..37515.45 rows=40009 width=4)"
"                Filter: ((entidade_t)::text ~~ '%car%'::text)"
"  SubPlan 2"
"    ->  Hash Join  (cost=36.48..7284.20 rows=298 width=4)"
"          Hash Cond: (ea.actividade = a.aid)"
"          ->  Seq Scan on t_entidade_actividade ea  (cost=0.00..5826.63 rows=378163 width=8)"
"          ->  Hash  (cost=36.46..36.46 rows=1 width=4)"
"                ->  Seq Scan on t_actividades a  (cost=0.00..36.46 rows=1 width=4)"
"                      Filter: ((actividade)::text ~~ '%car%'::text)"

查询 #2: 23661 行/~860 毫秒

-- EXPLAIN OUTPUT:
"QUERY PLAN"
"HashAggregate  (cost=45303.48..45706.55 rows=40307 width=4)"
"  ->  Append  (cost=0.00..45202.72 rows=40307 width=4)"
"        ->  Seq Scan on t_entidades  (cost=0.00..37515.45 rows=40009 width=4)"
"              Filter: ((entidade_t)::text ~~ '%car%'::text)"
"        ->  Hash Join  (cost=36.48..7284.20 rows=298 width=4)"
"              Hash Cond: (ea.actividade = a.aid)"
"              ->  Seq Scan on t_entidade_actividade ea  (cost=0.00..5826.63 rows=378163 width=8)"
"              ->  Hash  (cost=36.46..36.46 rows=1 width=4)"
"                    ->  Seq Scan on t_actividades a  (cost=0.00..36.46 rows=1 width=4)"
"                          Filter: ((actividade)::text ~~ '%car%'::text)"

因此，使用查询 #1 搜索 car 似乎要花很长时间……考虑到 SELECT eid FROM t_entidades 只需要大约 4 秒返回所有 350k+ 行，这很有趣。 ..

查询 #1 在不同步骤的 EXPLAIN 之间的唯一区别是对于 car 出现以下行:"-> Materialize (cost=37712.46.. 38269.55 行=40009 宽度=4)"

如果有人愿意解释为什么查询 #1 在最后一个例子中执行这么长时间以及在解释的每个步骤中到底发生了什么，我将不胜感激，因为我似乎从来没有得到它...

Answer 1

这是我看到的第一个 postgresql 执行计划，但看起来第一个计划是对 t_entidades 进行表扫描，然后对每一行执行以下所有操作，包括更多表扫描。

在第二个计划中，它仍然进行两次内部扫描，但对结果进行哈希聚合。

所以假设你的表中有 100 行，第一个计划进行 201 次表扫描，第二个计划进行 2 次。看图:-)