Обсуждение: 9.1 got really fast ;)

I have to share this, a statement that is finished before I even run it ;)

Limit  (cost=0.00..527.34 rows=20 width=4) (actual time=0.046..-2.436 rows=20 loops=1)
   Output: id, (count(*))
   Buffers: shared hit=191
   ->  GroupAggregate  (cost=0.00..12403455.78 rows=470416 width=4) (actual time=0.044..-2.448 rows=20 loops=1)
         Output: id, count(*)
         Buffers: shared hit=191
         ->  Index Scan using idx_pid on prodlink z  (cost=0.00..12373751.62 rows=5000000 width=4) (actual
time=0.021..-2.578rows=183 loops=1) 
               Output: id, pid, bid
               Buffers: shared hit=191

Total runtime: -2.368 ms   <<==== this is amazing ;)

This is with 9.1.1 on a Windows XP machine

On 15 Oct 2011, at 21:20, Thomas Kellerer wrote:
>
> Total runtime: -2.368 ms   <<==== this is amazing ;)
>
wow :)

> This is with 9.1.1 on a Windows XP machine
That explains it ;)

On 10/15/11 1:59 PM, Chris Travers wrote:
> Are you saying that Windows XP is the ultimate server OS for high
> performance PostgreSQL installations?  Are there optimizations that
> this platform can take advantage of, perhaps extending Pg timelines
> into actual time travel that are not available on other platforms?
>

powered by Neutrinos?





[*] wait, never mind, they think they found the error that lead to those
"FTL" results
      http://arxiv.org/PS_cache/arxiv/pdf/1110/1110.2685v1.pdf

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

On 10/15/11 1:59 PM, Chris Travers wrote:

Are you saying that Windows XP is the ultimate server OS for high performance PostgreSQL installations?  Are there optimizations that this platform can take advantage of, perhaps extending Pg timelines into actual time travel that are not available on other platforms?

powered by Neutrinos?

[*] wait, never mind, they think they found the error that lead to those "FTL" results
     http://arxiv.org/PS_cache/arxiv/pdf/1110/1110.2685v1.pdf

--

Edson Carlos Ericksson Richter SimKorp Informática Ltda
Fone:	(51) 3366-7964
Celular:	(51)9318-9766/(51) 8585-0796

On Sat, Oct 15, 2011 at 2:20 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
>
> Total runtime: -2.368 ms   <<==== this is amazing ;)
>
> This is with 9.1.1 on a Windows XP machine

I get something similar when I do select now()-query_start from
pg_stat_activity on my Ubuntu 10.04 / pg 8.3 servers.

Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Sat, Oct 15, 2011 at 2:20 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
>> Total runtime: -2.368 ms � <<==== this is amazing ;)

> I get something similar when I do select now()-query_start from
> pg_stat_activity on my Ubuntu 10.04 / pg 8.3 servers.

Within a transaction block that's not surprising, because now() is
defined as transaction start time not statement start time.

            regards, tom lane

On Sun, Oct 16, 2011 at 5:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Scott Marlowe <scott.marlowe@gmail.com> writes:
>> On Sat, Oct 15, 2011 at 2:20 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
>>> Total runtime: -2.368 ms   <<==== this is amazing ;)
>
>> I get something similar when I do select now()-query_start from
>> pg_stat_activity on my Ubuntu 10.04 / pg 8.3 servers.
>
> Within a transaction block that's not surprising, because now() is
> defined as transaction start time not statement start time.

No transaction block.

On 10/16/2011 04:39 PM, Scott Marlowe wrote:
> On Sun, Oct 16, 2011 at 5:24 PM, Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>> Scott Marlowe<scott.marlowe@gmail.com>  writes:
>>> On Sat, Oct 15, 2011 at 2:20 PM, Thomas Kellerer<spam_eater@gmx.net>  wrote:
>>>> Total runtime: -2.368 ms<<==== this is amazing ;)
>>> I get something similar when I do select now()-query_start from
>>> pg_stat_activity on my Ubuntu 10.04 / pg 8.3 servers.
>> Within a transaction block that's not surprising, because now() is
>> defined as transaction start time not statement start time.
> No transaction block.
>
Even stand-alone statements take place within a transaction - just not
an explicit one.

Cheers,
Steve

On 17 October 2011 17:25, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> On 10/16/2011 04:39 PM, Scott Marlowe wrote:
>>
>> On Sun, Oct 16, 2011 at 5:24 PM, Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>>>
>>> Scott Marlowe<scott.marlowe@gmail.com>  writes:
>>>>
>>>> On Sat, Oct 15, 2011 at 2:20 PM, Thomas Kellerer<spam_eater@gmx.net>
>>>>  wrote:
>>>>>
>>>>> Total runtime: -2.368 ms<<==== this is amazing ;)
>>>>
>>>> I get something similar when I do select now()-query_start from
>>>> pg_stat_activity on my Ubuntu 10.04 / pg 8.3 servers.
>>>
>>> Within a transaction block that's not surprising, because now() is
>>> defined as transaction start time not statement start time.
>>
>> No transaction block.
>>
> Even stand-alone statements take place within a transaction - just not an
> explicit one.

I doubt that more than 2.368 ms passed between the start of a
transaction and the stand-alone statement it's wrapping though. Not
impossible, but clock skew seems more likely to me.

--
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 17 October 2011 17:25, Steve Crawford <scrawford@pinpointresearch.com> wrote:
>> Even stand-alone statements take place within a transaction - just not an
>> explicit one.

> I doubt that more than 2.368 ms passed between the start of a
> transaction and the stand-alone statement it's wrapping though. Not
> impossible, but clock skew seems more likely to me.

We take some pains to ensure that the same gettimeofday reading is used
for both a transaction's start timestamp and the statement timestamp of
its first statement.  So I'm not sure what's up with Scott's report.
But in the OP's EXPLAIN case, that's the difference between successive
readings taken within the EXPLAIN code, so it's hard to see how to
explain it in any other way than "your system clock went backwards".
Possibly the underlying cause is clock skew between different processors
on a multiprocessor machine?

            regards, tom lane

At 11:44 PM 10/17/2011, Tom Lane wrote:
>Alban Hertroys <haramrae@gmail.com> writes:
> > On 17 October 2011 17:25, Steve Crawford
> <scrawford@pinpointresearch.com> wrote:
> >> Even stand-alone statements take place within a transaction - just not an
> >> explicit one.
>
> > I doubt that more than 2.368 ms passed between the start of a
> > transaction and the stand-alone statement it's wrapping though. Not
> > impossible, but clock skew seems more likely to me.
>
>We take some pains to ensure that the same gettimeofday reading is used
>for both a transaction's start timestamp and the statement timestamp of
>its first statement.  So I'm not sure what's up with Scott's report.
>But in the OP's EXPLAIN case, that's the difference between successive
>readings taken within the EXPLAIN code, so it's hard to see how to
>explain it in any other way than "your system clock went backwards".
>Possibly the underlying cause is clock skew between different processors
>on a multiprocessor machine?

Some years ago the early Athlon Athlon X2 CPUs had unsynced TSCs and
the OSes used the TSCs to speed up gettimeofday (somehow despite all
the advances in CPUs, chipsets etc, billions of transistors the
hardware bunch didn't help much for time keeping, yes there's HPET on
some motherboards but HPET isn't that great either AFAIK ).

I suppose this might be due to a different but still similar issue.

References:

<http://developer.amd.com/Membership/Print.aspx?ArticleID=38&web=http%3A%2F%2Fdeveloper.amd.com>http://developer.amd.com/Membership/Print.aspx?ArticleID=38&web=http%3A%2F%2Fdeveloper.amd.com


http://people.redhat.com/mingo/time-warp-test/time-warp-test.c

Regards,
Link.

On Mon, Oct 17, 2011 at 9:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alban Hertroys <haramrae@gmail.com> writes:
>> On 17 October 2011 17:25, Steve Crawford <scrawford@pinpointresearch.com> wrote:
>>> Even stand-alone statements take place within a transaction - just not an
>>> explicit one.
>
>> I doubt that more than 2.368 ms passed between the start of a
>> transaction and the stand-alone statement it's wrapping though. Not
>> impossible, but clock skew seems more likely to me.
>
> We take some pains to ensure that the same gettimeofday reading is used
> for both a transaction's start timestamp and the statement timestamp of
> its first statement.  So I'm not sure what's up with Scott's report.
> But in the OP's EXPLAIN case, that's the difference between successive
> readings taken within the EXPLAIN code, so it's hard to see how to
> explain it in any other way than "your system clock went backwards".
> Possibly the underlying cause is clock skew between different processors
> on a multiprocessor machine?

Could be.  That machine has 48 AMD 61xx series cores in it.