PostgreSQL 9.1 and 9.4 faster than 9.6

Question

We decided to migrate to 9.6 after some research about parallel query, so we brought up two identical machines with exactly the same hardware, kernel and others with PostgreSQL 9.1 and 9.6 respectively.

The 9.1 and 9.6 versions have the same configuration, except the parallel configuration that has 5 parallel workers in PostgreSQL 9.6.

The results are not as we are expecting for. At the start, it seems better than 9.1 for simple queries like select * from something where big_string_column ilike '%test%';

But when the queries start to get more complex, PostgreSQL 9.1 started to win and so I am searching for help. We tried to disable parallel query, but it did not help. I can't give more details about the database structure (very sorry), but it will help me if someone has experienced something similar and solved it.

The (obfuscated) query that I executed on both:

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;

The generated plan for 9.1 (explain analyze):

 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

The generated plan for 9.6 (yes, it is for the same query):

 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

In PostgreSQL 9.1 the run time of the query is about 1 or 2 seconds. For 9.6 it is more than 8 or 9 seconds. I checked the configuration a lot of times and they are identical now. I removed the parallel query, but it is still slower than 9.1. I even tried to penalize 9.1 by reducing work_mem and shared_buffers or increasing in 9.6, but 9.1 still wins. I ran vacuum analyze a lot of times.

I ran the test with the 9.4 and is acting like 9.1. I suspect that 9.5 will give the same results, and there is some performance bug in 9.6. I dropped the database and created it again in 9.6 - it is still slow. The worst thing, is that a lot of queries are working more slowly in 9.6, and a few queries are faster.

Is this difference caused by some kind of optimization that I can disable?

The specific version is 9.6.2.

Answer 1

It seems that PostgreSQL 9.6 needs a greater value of default_statistics_target than in 9.1, I suppose that is because the great number of query planner options in 9.6.

I increased it from 1000 to 2000, and then ran analyze.

Now it is working fine !