2

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.

  • 2
    You should post this on the postgres performance mailing list - maybe the developers have more ideas on how to tackle that. – a_horse_with_no_name May 5 '17 at 14:20
  • 1
    What collation are you using in both databases? You can see with \l+. – jjanes May 6 '17 at 5:28
  • I updated the answer as i found what i think is a true solution. Both are using pt_BR.UTF-8. – Luciano Andress Martini May 10 '17 at 15:16
3

UPDATE:

It seems that postgresql 9.6 needs a great value of default_target_statistics than 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 runned 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. – Luciano Andress Martini Jan 3 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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