>
> 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
------------------------------
