不同的查询，相同的结果(似乎)，完全不同的性能。为什么？

Question

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

搜索cartography时的结果

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

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 22；

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%'

搜索cart时的结果

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

查询#2: 715行/~450 715

搜索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只需要大约4s返回所有350k+行.

在不同的步骤中，查询#1的EXPLAIN之间唯一的区别是，对于car，出现了以下行：“->物化(cost=37712.46..38269.55 rows=40009 width=4)”

如果有人愿意解释为什么在最后一个示例中执行查询1花费了这么长时间，以及在解释的每一步到底发生了什么，这将是非常感谢的，因为我似乎从来没有得到它。

Answer 1

对我来说，query#1的计划是：

1. 扫描t_entidades，对于每一行：
   1. 通过扫描物化子集执行子计划1 (temp文件？)从t_entidades
   2. execute子计划2中通过检查由扫描t_entidade_actividade

生成的哈希表

​

“解释分析”可以告诉您实际运行步骤1.1和1.2的频率.如果步骤1.1中的扫描是从步骤1开始对每一行执行的，那么您的查询时间将增长O(n^2)，其中n是t_entidades中的行数，而用于每一次迭代1.1的临时空间将随着该表中匹配的数量的增加而增加。

您的查询2编写得更好，IMHO。这两组ID中的每一组都是以非常不同的方式生成的，因此将它们放在单独的查询中，并在最后使用UNION将它们合并在一起。它还删除了查询1中t_entidades的无用外部扫描，它只通过where子句中的ID。(这与PostgreSQL并不相关，但它也清楚地表明，这两个扫描可以并行运行，然后合并，但没关系)。

t_entidade_actividade.actividade可能需要一个索引吗？

Answer 2

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

在第二个计划中，它仍然进行两次内部扫描，但集中处理结果。

因此，假设表中有100行，第一个计划执行201次表扫描，第二个计划执行2次。

Answer 3

第一个查询非常奇怪，它只会混淆queryplanner。第一个子查询不应该是子查询，第二个子查询有一个应该是内部连接的左连接，但也可以在没有子查询的情况下编写。

第二个查询还有一个左连接，实际上是一个内部连接，检查WHERE条件。

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

在列aid和actividade上有索引吗？