Re: Sort time - Mailing list pgsql-performance

Hi,

Stephan Szabo wrote:

> On Sun, 17 Nov 2002, pginfo wrote:
>
> > Hi,
> >
> > Stephan Szabo wrote:
> >
> > > On Sat, 16 Nov 2002, pginfo wrote:
> > >
> > > > Hi,
> > > >
> > > > Tom Lane wrote:
> > > >
> > > > > Josh Berkus <josh@agliodbs.com> writes:
> > > > > > Here's a question: is the total size of the column a good indicator of the
> > > > > > sort_mem required?   Or does the rowsize affect it somehow?
> > > > >
> > > > > It will include all the data that's supposed to be output by the sort...
> > > > > both the key column(s) and the others.
> > > > >
> > > >
> > > > Hmm it is not clear for me.Let we have all data.
> > > > If I make sort by S.OP ( it is INT) it take < 6 sek for sort.
> > > > I think we move all this data anly the number of comparation is by INT. I think
> > > > the number of comparation
> > > > is ~ n * ln(n).
> > > > If we sort by S.IDS_xxx we have also n*ln(n) comparations but in
> > > > varchar(string).
> > > > I don't think that it can take 50 sek.
> > > >
> > > > Is it not so?
> > >
> > > Have you tried setting up another database in "C" locale and compared the
> > > timings there?  I'd wonder if maybe there's some extra copying going on
> > > given the comments in varstr_cmp.
> >
> > No, I do not have any info about it.I will see if it is possible ( the data are not
> > so simple).
> > If it is possible I will make the tests.
> > Have no one that have 700K row in thow tables?
> > It is simple to test:
> > 1. Run query that returns ~700K rows from this tables.
> > 2. Make sort.
> >
> > It is interest only the sort time!
>
> I can make a table of 700k rows and test it (and am generating 700k of
> random varchar rows), but I wouldn't hold great hope that this is
> necessarily a valid test since possibly any of OS, configuration settings
> and actual data (width and values) might have an effect on the results.
>

It is so.But the info will help.
If the sort time is 5-6 sek.(by me it is 50 sek) I will work on config and OS settings.
I am uning RH 7.3 at the moment. If anoder OS will have better performance I will make
the change.
But if the sort time is ~50 sek in any OS and config the problem will be in pg and I will
start to think about to
rewrite the sort part of src or migrate to anoder db(mysql or SAPdb. On oracle we have
super performance in sorting at the moment, but the idea is to move
the project to pg).

I think the sort is very important for any db.

Also it will be possible for me (in 1-2 days ) to install anoder box for tests and give
access to some one that can see the problem.
But as beginning it will be great to have more info about sort test results.

If any one have better idea I am ready to discuse it.

regards,
Ivan.

> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)