On Mon, May 8, 2017 at 5:06 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, May 8, 2017 at 4:24 PM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
>> On Mon, 8 May 2017 12:48:29 -0600
>> Scott Marlowe <scott.marlowe@gmail.com> 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.
