Bruce Momjian wrote:
>
> >
> > 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
And why not to implement several sort algorythms which
may be used in queries.
I think, somethink like GROUP BY ... USING psort - will be very
usefull. Is it possible?
------------------------------