Note: I have significantly re-worked this question based on the direction the commentators have taken me and what I have learned on the way.
I am setting up a new development database server. The new one is a 6th Gen i7-6700 with 16GB DDR4 memory and an Intel 750 SSD with PostgreSQL 9.6. The old one is a 4th gen i5-4570 with 16GB DDR3 memory and a Crucial SSD Raid 0 array with PostgreSQL 9.2. I benchmarked the 750 and it is much faster than the RAID. However, the read performance below 256KB is slower.
pg_restore to move the data and I ran
vacuum analyze on all databases.
The new computer should be quite a bit faster than the old one and in some types of queries is it, but in others it is several times slower. I also installed 9.2 on the new computer to try and isolate the database versions from the hardware. I kept the
postgresql.conf identical where possible (9.6 changes some wal settings.)
I set up
pg_stat_statements to get some statistics to narrow down the problem. I have 500+ schemas and 3000+ tables. The transformations I am running are creating new tables from some of these tables and updating those new tables from these 3000 other tables. I am bouncing all over this 50GB of data.
On the new server and 9.6
drop table, updates that affect many rows,
create table as are usually faster, up to twice as fast. But occasionally they will be several times slower. The new server running 9.2 sees a similar pattern, but the performance increases are not nearly as pronounced. Interestingly, when 9.6 is slower on some particular
vacuum, 9.2 will usually be similarly slow.
select queries that return few rows,
update queries that affect a single row are all usually 50%-300% slower in the new 9.6 server. 9.2 on the new server is also slower, but usually only 20%-30%. I do not know how PostgreSQL accesses the disk, but I suspect that the Intel 750's slower read performance on smaller files is having an effect as seen in the slower 9.2 speed. But that does not explain the 5x-10x performance degradation between 9.2 and 9.6 on the same server.
So my conclusion is 9.6 is significantly slower for certain types of queries.
I have run explain on many of the queries in question and the plans are identical.
Here are some representative examples (from
pg_stat_statements) of the queries that are slow. Note that they are all primary keyed, indexed vacuumed and analyzed and otherwise identical between the two databases. Also, I only selected queries that have at least 50 calls.
Update finalal Set jsondone=?, json=? Where id=? //20% slower SELECT * FROM archive_natrilis_2015_q3.natrilis_releases WHERE siteid = ? ORDER BY reporting_year ASC LIMIT ? // 404% slower SELECT * FROM work_narcra00_2016_q3.narcra_handler WHERE siteid = ? ORDER BY handler_sequence_number ASC LIMIT ? // 182% slower select * from listtypedesc // small 20 row table, 173% slower. select * from INFORMATION_SCHEMA.schemata where schema_name not like ? and schema_name not like ? AND schema_name like ? Order by schema_name Desc // 52% slower Select count(*) From work_allustis_2016_q3.allustis_details Where siteid = ? // 234% slower SELECT * FROM archive_naerns14_2015_q2.naerns14_material_involved WHERE siteid = ? LIMIT ? // 287% slower
Is my conclusion correct? And if so is there a 9.6 specific way to optimize these slow queries? If not, are there other debug actions to recommend?