Στις Wednesday 12 January 2011 18:45:20 ο/η Tom Lane έγραψε:
> 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:
>
You are absolutely right!
Changing kern.timecounter.hardware=TSC (from ACPI-fast) *solved* the slow explain analyze effect.
Now i get reasonable EXPLAIN ANALYZE times!
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.
Next step, if i am lucky tonight, i'll set up a new linux box with 9.0.2 and load
the same small (coming from FBSD_DEV) database.
So my problem was twofold : a) EXPLAIN ANALYZE performance, b) the particular slow query.
It seems there is only b) to solve. (see the previous email)
A lot of thanx (but stay tuned ;)
>
> regards, tom lane
>
--
Achilleas Mantzios