Re: Postgresql 9.0.2 explain analyze very slow (10x), compared to actual query time

Στις Wednesday 12 January 2011 18:45:20 ο/η Tom Lane έγραψε:
> Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)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