PostgreSQL 9.1 和 9.4 比 9.6 快

在对并行查询进行了一些研究后，我们决定迁移到 9.6，因此我们使用 PostgreSQL 9.1 和 9.6 分别启动了具有完全相同硬件、内核和其他硬件的两台相同的机器。

9.1 和 9.6 版本具有相同的配置，除了在 PostgreSQL 9.6 中具有 5 个并行工作器的并行配置。

结果并不像我们预期的那样。一开始，对于像这样的简单查询，它似乎比 9.1 更好select * from something where big_string_column ilike '%test%';

但是当查询开始变得更复杂时，PostgreSQL 9.1 开始获胜，所以我正在寻求帮助。我们试图禁用并行查询，但没有帮助。我无法提供有关数据库结构的更多详细信息（非常抱歉），但如果有人经历过类似的事情并解决了它，它将对我有所帮助。

我在两者上执行的（混淆的）查询：

SELECT case when lbipro = 1 then 'PEM'
            when lbipro = 2 then 'CRE'
            when lbipro = 3 then 'COM'
            when lbipro = 4 then 'REC'
            when lbipro = 6 then 'DES'
            END pnom, 
            lbipro, 
            sum(lbival) as lim,
            sum(coalesce(CASE WHEN lbisal > 0 THEN lbisal END,0)) dsp,
            sum(coalesce(CASE WHEN lbisal <= 0 THEN lbisal END,0)) exc,
            sum(coalesce(obiave,0)) as tom
FROM "lbi"
LEFT JOIN (SELECT sum(obiave) as obiave, obimat, obipro, obiscr
           FROM   obi
           WHERE  obicop = '022017'
           GROUP BY obimat, obipro, obiscr) as oo 
ON        oo.obimat = lbimat 
and       oo.obipro = lbipro
WHERE     lbicon = '5' 
AND       lbicop = '022017'
GROUP BY  "lbipro"
ORDER BY  "lbipro" ASC;

为 9.1 生成的计划（解释分析）：

 Sort  (cost=231411.25..231411.26 rows=1 width=46) (actual time=2389.223..2389.223 rows=3 loops=1)
   Sort Key: lbi.lbipro
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=231411.22..231411.24 rows=1 width=46) (actual time=2389.209..2389.214 rows=3 loops=1)
         ->  Hash Right Join  (cost=193708.93..223165.41 rows=471189 width=46) (actual time=1698.385..1950.493 rows=458094 lo$
               Hash Cond: (((obi.obimat)::text = (lbi.lbimat)::text) AND (obi.obipro = lbi.lbipro))
               ->  HashAggregate  (cost=66042.93..66261.10 rows=21817 width=55) (actual time=470.412..536.613 rows=125480 loo$
                     ->  Seq Scan on obi  (cost=0.00..63861.29 rows=218164 width=55) (actual time=0.137..352.910 rows=215512 $
                           Filter: ((obicop)::text = '022017'::text)
               ->  Hash  (cost=120598.17..120598.17 rows=471189 width=32) (actual time=1227.654..1227.654 rows=458094 loops=1)
                     Buckets: 65536  Batches: 1  Memory Usage: 29292kB
                     ->  Seq Scan on lbi  (cost=0.00..120598.17 rows=471189 width=32) (actual time=5.109..1093.313 rows=45809$
                           Filter: ((lbicon = 5) AND ((lbicop)::text = '022017'::text))
 Total runtime: 2389.494 ms

为 9.6 生成的计划（是的，它是针对同一个查询的）：

 GroupAggregate  (cost=244410.91..256931.05 rows=3 width=164) (actual time=7070.306..7547.298 rows=3 loops=1)
   Group Key: lbi.lbipro
   ->  Merge Left Join  (cost=244410.91..248891.54 rows=459395 width=46) (actual time=6789.774..7290.870 rows=458094 loops=1)
         Merge Cond: ((lbi.lbipro = oo.obipro) AND ((lbi.lbimat)::text = (oo.obiave)::text))
         ->  Sort  (cost=163803.11..164951.59 rows=459395 width=32) (actual time=5102.693..5220.945 rows=458094 loops=1)
               Sort Key: lbi.lbipro, lbi.lbimat
               Sort Method: quicksort  Memory: 48077kB
               ->  Seq Scan on lbi  (cost=0.00..120598.44 rows=459395 width=32) (actual time=6.580..1341.290 rows=458094 loop$
                     Filter: ((lbicon = 5) AND ((lbicop)::text = '022017'::text))
                     Rows Removed by Filter: 3518238
         ->  Sort  (cost=80607.80..80945.86 rows=135222 width=54) (actual time=1687.073..1709.739 rows=125480 loops=1)
               Sort Key: oo.obipro, oo.obiave
               Sort Method: quicksort  Memory: 12876kB
               ->  Subquery Scan on oo  (cost=66041.03..69083.53 rows=135222 width=54) (actual time=695.702..803.308 rows=125$
                     ->  HashAggregate  (cost=66041.03..67731.31 rows=135222 width=81) (actual time=695.701..794.818 rows=125$
                           Group Key: obi.obiave, obi.obipro, obi.obiscr
                           ->  Seq Scan on obi  (cost=0.00..63861.41 rows=217962 width=55) (actual time=0.193..541.695 rows=2$
                                 Filter: ((obicop)::text = '022017'::text)
                                 Rows Removed by Filter: 1728890
 Planning time: 1.528 ms
 Execution time: 7555.976 ms

在 PostgreSQL 9.1 中，查询的运行时间约为 1 或 2 秒。对于 9.6，它超过 8 或 9 秒。我检查了很多次配置，现在它们是相同的。我删除了并行查询，但它仍然比 9.1 慢。我什至试图通过减少或增加 9.6 来惩罚 9.1 work_mem，shared_buffers但 9.1 仍然获胜。我跑vacuum analyze了很多次。

我用 9.4 进行了测试，表现得像 9.1。我怀疑 9.5 会给出相同的结果，并且 9.6 中存在一些性能错误。我删除了数据库并在 9.6 中再次创建它 - 它仍然很慢。最糟糕的是，很多查询在 9.6 中运行得更慢，而少数查询更快。

这种差异是由我可以禁用的某种优化引起的吗？

具体版本为9.6.2。