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.

Thanks!