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.
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!