Re: [HACKERS] \dt and disk access\

>
> Bruce Momjian wrote:
> > OK, I think I have some more information on this.
> > OK, now for some times.  On my machine (PP200 running BSD/OS), the
> > startup/shutdown time for the database is 0.15 seconds, so I will
> > subtract that time to show the incremental elapsed wallclock time for
> > each command:
> >
> >         Select without order by:        0.01
> >
> >         Select with order by:           0.30
> >
> >         Create Table:                   0.11
> >         Create Index:                   0.54
> >
> >         Insert into Table:              0.01
> >
> > These are all times on a table with one integer column, and one row.
> >
> > As you can see, the ORDER BY has slowed the SELECT by a factor of 30.
> > You can actually create three tables in the same time, or insert 30
> > rows.
> >
> > This looks like a pretty serious performance problem.
>
> By having a small table, you are really just measuring the overhead in
> setting up the sort.

I realize this.  The psort() creates one or multiple DISK files to do
the sort. That is the setup killer.  80% of ORDER BY's don't need this,
and the overhead is huge compared to the time to perform the query.

I say this because my disk light was almost solid on when doing a select
with an ORDER BY returning one row.  I knew this was improper.

> Try something with a _lot_ of data, and try to have
> the data inserted randomly if possible. Another thing to check is the
> fundamental time it takes to actually do sorting (for example, put the
> same data into a little test program which calls qsort(), or find the
> Knuth algorithm and call that directly). Sorts aren't (usually) cheap!

See above.

- --
Bruce Momjian
maillist@candle.pha.pa.us

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