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, 
            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.

  • 2
    You should post this on the postgres performance mailing list - maybe the developers have more ideas on how to tackle that.
    – user1822
    May 5, 2017 at 14:20
  • 1
    What collation are you using in both databases? You can see with \l+.
    – jjanes
    May 6, 2017 at 5:28
  • I updated the answer as i found what i think is a true solution. Both are using pt_BR.UTF-8. May 10, 2017 at 15:16
  • As noted in the answer below, changing the default statistics target helped mine a lot. The default was only 100 on RDS, so I put it up to 2000 as was recommended. The one thing to note is that the parameter name is actually default_statistics_target not default_target_statistics. NB: This would be a comment rather than a separate post, but I don't have permissions to comment.
    – Julie G
    Feb 10, 2020 at 20:17

1 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 !

  • Tks! You're welcome! You also can put vacuumdb -a on your crontab to run the analyze with the new statistics every night for example, if you think autovacuum is not doing the expected job. Jan 3, 2019 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.