Re: Execution variability

Vincenzo Romano wrote:
>>>> The very same query on the very same db shows very variable
>>>> timings. I'm the only one client on an unpupolated server so I'd
>>>> expect a rather constant timing.
>>> What's really weird is that after some time the timings get back
>>> to normal. With no explicit action. Then, later, timings get
>>> worse again.

> From the "top" command (I'm running Linux) the only process that jumps
> high with the load is just the postrgres instance managing the SQL
> connection.
> I agree about "something else must be happening in the background".
> All rthe available RAM gets used as well as some swap.
> During "fast" operations the used RAM remains low and no swap
> happens.

That suggests it's not the "same query" that's causing problems. If
you're going into swap then performance will vary wildly. You may have
allocated more memory to PostgreSQL than is available on the machine.

> I would exclude any other "system" process.
>
> How can I log what the PGSQL is actually doing?

See the "when to log" and "what to log" parts of this:
http://www.postgresql.org/docs/8.2/static/runtime-config.html

As postgres (or other superuser) you can do:
ALTER DATABASE <db> SET log_min_duration_statement = 1000;
That will log all statements that take longer than 1 second.

Alternatively log_statement = 'all' will show all queries executed.

You probably want to read the section on "Resource Consumption" linked
above too. In particular work_mem is *per sort*, which means one query
can use several times the amount set.

If you post the values for the settings listed in chapter 17.4.1 of the
manuals and a description of what your machine is like, what else it is
doing then we might be able to suggest some changes.

--
Richard Huxton
Archonet Ltd