Re: Timing a query

On 27 Nov 2001, Elizabeth O'Neill wrote:

>Can someone tell me if there is a way of getting the time a query has taken
>to run to display after the query results ( where in gives the row
>count).

I am new myself and this is one of the things that I looked for without
much luck.

A couple of thoughts.
-You could restart postgresql and send the output of the server to a file.
Something like "pg_ctl restart >/var/log/pg.log". If you only have a few
users, or even better just you, then the output of that file will have the
type of ino you are looking for. However this won't work well if you have
different users since you won't easily know whose querie's result you are
looking at.

-You could create a "log table" where you do insert's before and after
your operation. I just started playing with this yesterday with mixed
results, but it would be something like:

insert into mergelogs values ('hraces', 'start load');
copy hraces from '/vol2/stmp1/hraces.txt' with NULL as '';
insert into mergelogs values ('hraces', 'completed load');
vacuum hraces;
insert into mergelogs values ('hraces', 'ran vacuum');

That worked for me, except that I wanted an insert coupled with a
begin/end transaction and that didn't work. The time of the insertion was
the same time as the time of the begin. In other words I was tying
something like
insert into mergelogs values ('hraces', 'start load');
begin transaction;
copy hraces from '/vol2/stmp1/hraces.txt' with NULL as '';
insert into mergelogs values ('hraces', 'completed load');
end transaction;
vacuum hraces;
insert into mergelogs values ('hraces', 'ran vacuum');

I wanted the "completed load" message to only appear if the copy worked,
but the problem was that the time been logged seemed the same as the time
the begin started.

IMPORTANT DETAIL: On the mergelogs table I have a field with
"DEFAULT now" so when I do an insert the time is recorded.