1

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!

  • @jjanes, I did pg_dump and pg_restore – CV-Gate Feb 2 '18 at 6:43
  • Did you run a manual vacuum analyze afterwards? Autovac could take a long time to get around to all the tables. Also, can you show the full explain analyze of the plans (using explain.depesz.com)? I don't think we can figure it out based on the abbreviated tables you posted. – jjanes Feb 2 '18 at 6:51
  • Yes. I did run manual vacuum analyze. Old DB query: explain.depesz.com/s/3Le6 New DB query: explain.depesz.com/s/7rwx The queries are very verbose and are generated by an ORM but I don't want to focus on how it's done because it is the same for both environments and the performance change is huge. Thanks for your help. – CV-Gate Feb 2 '18 at 7:05
  • Did an index get missed on the new server during the restore? The hash right join step got way more expensive. I also notice the new query is doing an external merge on disk with size listed as 2538216kB. That's 2.4 GB What's going on in that query if the DB is < 20MB? Are you using FDW? – RustProof Labs Feb 5 '18 at 20:33
  • On that same step, the row count jumped from 98,665 on the old server to 1.5M on the new server. – RustProof Labs Feb 5 '18 at 20:35

Your Answer

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

Browse other questions tagged or ask your own question.