Thread: better performance on poorer machine?
Hi, I have a database on a test server with queries that perform terribly. Trying to fix this problem, I copied the database(using pg_dump) to my laptop, and reran tests there. The same queries perform perfectly on my laptop. I have tried to use the same postgresql.conf, and run ANALYZE and even VACUUM ANALYZE on the databases, but the problem remains. EXPLAIN shows a somewhat different query plan for each database. Now I have no idea what to do next. How can I go about trying to find the cause of this? I can see no other explanation thanhardware issues, but in theory, the test servers (there are more than one, with the same performance problems) shouldbe better than my laptop in all ways. Have I missed something obvious? regards, Vegard
On 05/13/2014 08:48 AM, Vegard Bønes wrote:
First suspicion is of course a lack of indexing on the original. I would compare the full definitions of the tables involved.Hi, I have a database on a test server with queries that perform terribly. Trying to fix this problem, I copied the database (using pg_dump) to my laptop, and reran tests there. The same queries perform perfectly on my laptop. I have tried to use the same postgresql.conf, and run ANALYZE and even VACUUM ANALYZE on the databases, but the problem remains. EXPLAIN shows a somewhat different query plan for each database. Now I have no idea what to do next. How can I go about trying to find the cause of this? I can see no other explanation than hardware issues, but in theory, the test servers (there are more than one, with the same performance problems) should be better than my laptop in all ways. Have I missed something obvious? regards, Vegard
On 13 May 2014 16:48, Vegard Bønes <vegard.bones@met.no> wrote: > I have a database on a test server with queries that perform terribly. Trying to fix this problem, I copied the database(using pg_dump) to my laptop, and reran tests there. The same queries perform perfectly on my laptop. We can but guess, but... Quite possibly your laptop is not actually a poorer machine for single uncomplicated queries. If it's cores are faster than the test servers, than your laptop would out-perform the server for such queries. Once you get other users starting to run queries as well, turning the load into a parallel load, the server will probably turn out faster again. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Alban Hertroys <haramrae@gmail.com> writes: > On 13 May 2014 16:48, Vegard Bønes <vegard.bones@met.no> wrote: >> I have a database on a test server with queries that perform terribly. Trying to fix this problem, I copied the database(using pg_dump) to my laptop, and reran tests there. The same queries perform perfectly on my laptop. > We can but guess, but... Less guess-worthy answers might be possible if you provide the information suggested here: https://wiki.postgresql.org/wiki/Slow_Query_Questions regards, tom lane
Hi, all. I found the problem - it was merely a poorly written query, which for some reason was less terrible on my laptop. Lookingat http://explain.depesz.com/ helped me solve the issue. Performance is now back to normal. It seems this was a classic case of pebcak. Thank you for your help and time! VG ----- Original Message ----- Fra: "Alban Hertroys" <haramrae@gmail.com> Til: "Vegard Bønes" <vegard.bones@met.no> Kopi: pgsql-general@postgresql.org Sendt: 13. mai 2014 17:49:28 Emne: Re: [GENERAL] better performance on poorer machine? On 13 May 2014 16:48, Vegard Bønes <vegard.bones@met.no> wrote: > I have a database on a test server with queries that perform terribly. Trying to fix this problem, I copied the database(using pg_dump) to my laptop, and reran tests there. The same queries perform perfectly on my laptop. We can but guess, but... Quite possibly your laptop is not actually a poorer machine for single uncomplicated queries. If it's cores are faster than the test servers, than your laptop would out-perform the server for such queries. Once you get other users starting to run queries as well, turning the load into a parallel load, the server will probably turn out faster again. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.