Re: Sort time - Mailing list pgsql-performance
From | pginfo |
---|---|
Subject | Re: Sort time |
Date | |
Msg-id | 3DD75041.64B20817@t1.unisoftbg.com Whole thread Raw |
In response to | Re: Sort time (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
Responses |
Re: Sort time
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
|
List | pgsql-performance |
Hi, Stephan Szabo wrote: > On Sat, 16 Nov 2002, 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. > > On my not terribly powerful or memory filled box, I got a time of about > 16s after going through a couple iterations of raising sort_mem and > watching if it made temp files (which is probably a good idea to check as > well). The data size ended up being in the vicinity of 100 meg in my > case. The time is very good! It is very good idea to watch the temp files. I started the sort_mem to 32 mb (it is 256 on the production system) and I see 3 temp files. The first is ~ 1.8 mb. The second is ~55 mb and the last is ~150 mb. Also I removed the bigest as size fileds from my query but got only litle improvemen. regards, ivan.
pgsql-performance by date: