Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> ���� Wednesday 12 January 2011 17:07:53 �/� Tom Lane ������:
>> Right offhand I'd wonder whether that was more bound by gettimeofday or
>> by printf. Please try it without printf in the loop.
> Changed that to smth like: micros_total = micros_total + (double) micros;
> instead of the printf to beat any compiler optimization, and still linux runs at light speed:
> FBSD_TEST : user 0.089s, sys 1.4s
> FBSD_DEV : user 0.183s, sys 3.8s
> LINUX_PROD : user 0.168s, sys 0s
Well, there's your problem all right: the FBSD_DEV system takes 22X
longer to execute gettimeofday() than the LINUX_PROD system. The
particular plan that 9.0 is choosing is especially vulnerable to this
because it involves a whole lot of calls of the Materialize plan node:
> -> Nested Loop (cost=3390.49..9185.73 rows=1 width=4) (actual
time=279.916..93280.499rows=1824 loops=1)
> Join Filter: (msold.marinerid = mold.id)
> -> Index Scan using mariner_pkey on mariner mold (cost=0.00..1957.89
rows=15914width=4) (actual time=0.009..38.449 rows=15914 loops=1)
> Filter: ((marinertype)::text = 'Mariner'::text)
> -> Materialize (cost=3390.49..6989.13 rows=1 width=8) (actual
time=0.013..2.881rows=1888 loops=15914)
You've got 1888 * 15914 * 2 gettimeofday() calls needed to count up the
time spent in Materialize, which at 3.8usec apiece adds up to ... hmm
... I get 228 seconds, which is more than the reported runtime. So I
think there's something wrong with your measurement of the gettimeofday
cost. But I wouldn't be a bit surprised to hear that the true cost of
gettimeofday on that machine is a microsecond or so, in which case all
of the EXPLAIN ANALYZE time bloat is indeed due to that.
This problem has been discussed repeatedly on the pgsql-performance
list, which is where your question really belongs, not here. The short
of it is that cheap motherboards tend to provide cheap clock hardware
that takes about a microsecond to read. It also helps to be running
an OS that is able to read the clock in userspace without invoking a
kernel call. It looks like your Linux box is winning on both counts
compared to your BSD boxes.
regards, tom lane