Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> 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.499 rows=1824 loops=1) > Join Filter: (msold.marinerid = mold.id) > -> Index Scan using mariner_pkey on mariner mold (cost=0.00..1957.89 rows=15914 width=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.881 rows=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 -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
