On Mon, May 8, 2017 at 5:06 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > On Mon, May 8, 2017 at 4:24 PM, Vincent Veyron <vv.lists@xxxxxxxxxx> wrote: >> On Mon, 8 May 2017 12:48:29 -0600 >> Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: >>> > -Why are regular queries much faster on this same server? >>> >>> That's a whole nother subject. Most likely the faster machine can fit >>> the whole db in memory, or has much faster memory, or the whole >>> dataset is cached etc etc. >>> >> >> The dataset is small (35 MB) and both servers have 4GB memory. It appears to be faster on the Online server. > > Yeah it fits in memory. Select queries will only hit disk at bootup. > > First machine > SNIP >> Speed: 1066 MHz > SNIP >> Configured Clock Speed: 1066 MHz > > Second machine > >> Speed: 1600 MHz > SNIP >> Configured Clock Speed: 1333 MHz > > Yeah the second machine likely has a noticeably faster CPU than the > first as well. It's about two years younger so yeah it's probably just > cpu/mem that's making it fast. OK went back and looked at your original post. I seems like those two queries that are 10 and 20 ms have essentially the same plan on similar sized dbs, so it's reasonable to assume the newer machine is about twice as fast. Without seeing what your test sql file does I have no idea what the big difference in the other direction. You'll have to pull out and run the individual queries, or turn on auto explain or something to see the plans and compare. A lot of time it's just some simple tuning in postgresql.conf or maybe a database got an alter database on it to change something? Either way use show all; to compare settings and get explain (analyze) off of the slow queries. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
