From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: Postgresql 9.0.2 explain analyze very slow (10x), compared to actual query time |
Date: | 2011-01-13 14:13:23 |
Message-ID: | 201101131613.23428.achill@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Στις 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
From | Date | Subject | |
---|---|---|---|
Next Message | Devrim GÜNDÜZ | 2011-01-13 20:24:20 | Re: graphical admin tool for RedHat 6 |
Previous Message | Achilleas Mantzios | 2011-01-13 12:27:26 | Re: Postgresql 9.0.2 explain analyze very slow (10x), compared to actual query time |