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