From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: Re: Postgresql 9.0.2 explain analyze very slow (10x), compared to actual query time
Date: Thu, 13 Jan 2011 16:13:23 +0200
Message-ID: <201101131613.23428.achill@matrix.gatewaynet.com> (raw)
In-Reply-To: <27365.1294850720@sss.pgh.pa.us>
References: <201101111753.22602.achill@matrix.gatewaynet.com>
<201101121817.11143.achill@matrix.gatewaynet.com>
<27365.1294850720@sss.pgh.pa.us>
Óôéò 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: Postgresql 9.0.2 explain analyze very slow (10x), compared to actual query time
In-Reply-To: <201101131613.23428.achill@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox