Re: [HACKERS] \dt and disk access

Under BSD/OS 2.0 and up (don't know about 1.x), it should be possible
to start up the postmaster thus:
ktrace -i postmaster >& Log

This will trace all calls to the kernel (and generate a good deal of
output, as well as slow things down a lot).

Run your psql command.
Kill -15 the postmaster.

In the same directory where you started the postmaster, you can run
`kdump | less`.  Down toward the bottom of the file, you should be
able to track down your postgress process and what it was doing.

If you find it doing a lot of activity with /tmp, you might try mounting
/tmp as a memory file system.

I realize this isn't really an answer to the question "why is there
disk activity", but it should at least show "what postgres is doing".

> Well....Is the sorting algorithm using temp files to do the sorting? If
> not, it would be interesting to see the difference in Purify output when
> running an identical query with sorting and without...
> I'll probably run it on Saturday that way just to (hopefully) eliminate
> leaks and such from possible causes...
>
> =+=------------------------/\---------------------------------=+=
>        Igor Natanzon      |**|   E-mail: igor@sba.miami.edu
> =+=------------------------\/---------------------------------=+=
>
> On Sat, 21 Jun 1997, Bruce Momjian wrote:
>
> > >
> > > Can someone suggest why running the \dt comand in psql causes so many
> > > disk accesses?
> > >
> > > I am seeing some queries causing a lot of disk access that doesn't make
> > > sense to me.
> > >
> > > I am running my server with fsync off.
> >
> > I now realize the unusual disk activity is caused by any query that uses
> > an ORDER BY.  A query that returns three rows takes MUCH longer with the
> > ORDER BY than without it, 0.20 seconds vs. 0.61 seconds, three times
> > longer.
> >
> > The query is:
> >
> >     select * from matter
> >
> > AND
> >
> >     select * from matter order by matter
> >
> > The table has three short text columns.
> >
> >
> > Would anyone like to comment on why ORDER BY is doing this?  It
> > certainly slows down the query.  I am running BSD/OS.
> >
> > This is of course using PostgreSQL 6.1.
> >
> > --
> > Bruce Momjian
> > maillist@candle.pha.pa.us
> >
>
>


 -Erik

Erik Bennett                    bennett@corp.oneworld.com

------------------------------