This is the situation: I'm using Postgres 9.6 and had to migrate the database to a new server. This database is quite small, about 18MB in 25 tables but most of the records must be pulled in one query with 11 joins. In the old server, everything was working well but after the migration and importing the database, this query takes 4 minutes.
Making explain analyze I found two important differences:
1) On the old database, Index Only Scan was triggered:
node type count sum of times % of query Hash 7 0.392 ms 0.0 % Hash Left Join 6 68.972 ms 0.7 % Hash Right Join 1 0.296 ms 0.0 % Index Only Scan 1 0.342 ms 0.0 % Index Scan 4 2.695 ms 0.0 % Materialize 3 44.628 ms 0.4 % Merge Left Join 4 24.674 ms 0.2 % Merge Right Join 1 22.565 ms 0.2 % Nested Loop Left Join 2 0.822 ms 0.0 % Seq Scan 10 0.526 ms 0.0 % Sort 4 10,133.864 ms 97.2 % Unique 1 127.007 ms 1.2 %
Not in the new one:
node type count sum of times % of query Hash 10 254.372 ms 0.1 % Hash Left Join 8 114.429 ms 0.0 % Hash Right Join 2 1,907.899 ms 0.8 % Index Scan 3 8.785 ms 0.0 % Materialize 2 9.646 ms 0.0 % Merge Left Join 3 42.783 ms 0.0 % Nested Loop Left Join 1 1.322 ms 0.0 % Seq Scan 12 2.404 ms 0.0 % Sort 3 245,834.765 ms 97.3 % Unique 1 4,462.125 ms 1.8 %
2) In the old database, only columns from the main table were Sort Keys in the Sort Node:
Sort Key: projects.id, projects.project_uid, projects.status, projects.name, projects.scale
But Postgres is taking every column of the select statements as Sort Key in the new server:
Sort Key: projects.id, projects.project_uid, projects.status, projects.name, projects.scale, organizations.id, organizations.name, countries.id...
and so on, about 50 sort keys and the Sort Node takes 98% of the time.
Autovacuum is enabled and I checked it for all the tables.
I don't know what is causing this, maybe Postgres is taking every column as a Sort Key because there is no Index Only Scan or the other way around, or maybe another reason. I would like to know your opinion about this, I'm not a DBA and I'm not finding a solution.