Re: Why is sorting on two columns so slower thansortingon one column? - Mailing list pgsql-hackers

<br /><br /><div class="gmail_quote">On Thu, Dec 23, 2010 at 10:26 AM, Tom Lane <span dir="ltr"><<a
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span>wrote:<br /><blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><div class="im">Kenneth
Marshall<<a href="mailto:ktm@rice.edu">ktm@rice.edu</a>> writes:<br /> > On Thu, Dec 23, 2010 at 10:42:26PM
+0800,Li Jie wrote:<br /></div><div class="im">>> But in the last query that sorts on "id",  since the query
selectsall the columns for output, the actual sorted size is the same, and the only difference is the comparison cost.
Thequery sorting on two columns needs to do twice the comparison. Am I right?<br /><br /></div><div class="im">> I
thinkyou are right. Sorry for the confusion.<br /><br /></div>I doubt the cost of comparing two integers is the issue
here;rather<br /> it's more likely one of how many merge passes were needed.  You could<br /> find out instead of just
speculatingby turning on trace_sort and<br /> comparing the log outputs.<br /><br />                        regards,
tomlane<br /></blockquote></div><br />I follow your advice and set the trace_sort, here is the two queries result, the
logoutputs looks similar and  they seem to use the same number of tapes. But the time is different. Could you have a
look:<br/><br /><span id="internal-source-marker_0.16665964164891722" style="font-size: 11pt; font-family: Arial;
color:rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline;">postgres=# explain analyze select * from big_wf order by id;</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">
                                                     QUERY PLAN                                                      
 </span><br/><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent;
font-weight:normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">-------------------------------------------------------------------------------------------------------------------------</span><br
/><spanstyle="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline;"> Sort  (cost=565525.45..575775.45
rows=4100000width=8) (actual time=28102.985..39351.309 rows=4100000 loops=1)</span><br /><span style="font-size: 11pt;
font-family:Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">   Sort Key: id</span><br /><span style="font-size: 11pt; font-family:
Arial;color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline;">   Sort Method:  external merge  Disk: 72048kB</span><br /><span style="font-size:
11pt;font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">   ->  Seq Scan on big_wf  (cost=0.00..59142.00 rows=4100000
width=8)(actual time=9.190..7262.789 rows=4100000 loops=1)</span><br /><span style="font-size: 11pt; font-family:
Arial;color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline;"> Total runtime: 42953.855 ms</span><br /><span style="font-size: 11pt; font-family:
Arial;color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline;">(5 rows)</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0,
0);background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">STATEMENT: explain analyze select * from big_wf order by id;</span><br /><span style="font-size: 11pt;
font-family:Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">LOG:  begin tuple sort: nkeys = 1, workMem = 20480, randomAccess =
f</span><br/><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent;
font-weight:normal; font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze
select* from big_wf order by id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">LOG: switching to external sort with 74 tapes: CPU 0.29s/0.28u sec elapsed 0.71 sec</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf
orderby id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">LOG:  finished
writingrun 1 to tape 0: CPU 0.68s/2.12u sec elapsed 3.41 sec</span><br /><span style="font-size: 11pt; font-family:
Arial;color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf order by id;</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">LOG:  finished writing run 2 to tape 1: CPU
1.22s/4.24usec elapsed 6.53 sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">STATEMENT: explain analyze select * from big_wf order by id;</span><br /><span style="font-size: 11pt;
font-family:Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">LOG:  finished writing run 3 to tape 2: CPU 1.67s/6.38u sec elapsed
9.67sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:
 explainanalyze select * from big_wf order by id;</span><br /><span style="font-size: 11pt; font-family: Arial; color:
rgb(0,0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline;">LOG:  finished writing run 4 to tape 3: CPU 2.22s/8.61u sec elapsed 12.93 sec</span><br
/><spanstyle="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze select * from
big_wforder by id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">LOG:  finished
writingrun 5 to tape 4: CPU 2.72s/10.80u sec elapsed 16.09 sec</span><br /><span style="font-size: 11pt; font-family:
Arial;color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf order by id;</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">LOG:  finished writing run 6 to tape 5: CPU
3.23s/12.86usec elapsed 19.12 sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">STATEMENT: explain analyze select * from big_wf order by id;</span><br /><span style="font-size: 11pt;
font-family:Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">LOG:  finished writing run 7 to tape 6: CPU 3.81s/15.02u sec elapsed
23.84sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:
 explainanalyze select * from big_wf order by id;</span><br /><span style="font-size: 11pt; font-family: Arial; color:
rgb(0,0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline;">LOG:  finished writing run 8 to tape 7: CPU 4.36s/17.13u sec elapsed 27.05 sec</span><br
/><spanstyle="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze select * from
big_wforder by id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">LOG:
 performsortstarting: CPU 4.38s/17.20u sec elapsed 27.15 sec</span><br /><span style="font-size: 11pt; font-family:
Arial;color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf order by id;</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">LOG:  finished writing run 9 to tape 8: CPU
4.39s/17.88usec elapsed 27.97 sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">STATEMENT: explain analyze select * from big_wf order by id;</span><br /><span style="font-size: 11pt;
font-family:Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">LOG:  finished writing final run 10 to tape 9: CPU 4.39s/17.88u sec
elapsed27.97 sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:
 explainanalyze select * from big_wf order by id;</span><br /><span style="font-size: 11pt; font-family: Arial; color:
rgb(0,0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline;">LOG:  performsort done (except 10-way final merge): CPU 4.39s/17.98u sec elapsed 28.10
sec</span><br/><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent;
font-weight:normal; font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze
select* from big_wf order by id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">LOG: external sort ended, 9006 disk blocks used: CPU 8.01s/27.02u sec elapsed 42.92 sec</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf
orderby id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;"></span><br
/><spanstyle="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline;"></span><br /><span style="font-size: 11pt;
font-family:Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">postgres=# explain analyze select * from big_wf order by
age,id;</span><br/><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;"> Sort
 (cost=565525.45..575775.45rows=4100000 width=8) (actual time=43709.851..57048.645 rows=4100000 loops=1)</span><br
/><spanstyle="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline;">   Sort Key: age, id</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">   Sort Method:  external merge  Disk:
72048kB</span><br/><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">   ->  Seq
Scanon big_wf  (cost=0.00..59142.00 rows=4100000 width=8) (actual time=10.090..7075.208 rows=4100000 loops=1)</span><br
/><spanstyle="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline;"> Total runtime: 60721.824 ms</span><br
/><spanstyle="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline;"></span><br /><span style="font-size: 11pt;
font-family:Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf order by
age,id;</span><br/><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">LOG:  begin
tuplesort: nkeys = 2, workMem = 20480, randomAccess = f</span><br /><span style="font-size: 11pt; font-family: Arial;
color:rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline;">STATEMENT:  explain analyze select * from big_wf order by age,id;</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">LOG:  switching to external sort with 74 tapes:
CPU0.28s/0.30u sec elapsed 0.67 sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">STATEMENT: explain analyze select * from big_wf order by age,id;</span><br /><span style="font-size: 11pt;
font-family:Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">LOG:  finished writing run 1 to tape 0: CPU 0.71s/3.63u sec elapsed
4.90sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:
 explainanalyze select * from big_wf order by age,id;</span><br /><span style="font-size: 11pt; font-family: Arial;
color:rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline;">LOG:  finished writing run 2 to tape 1: CPU 1.30s/7.53u sec elapsed 10.30 sec</span><br
/><spanstyle="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze select * from
big_wforder by age,id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">LOG: finished writing run 3 to tape 2: CPU 1.88s/11.36u sec elapsed 15.39 sec</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf
orderby age,id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">LOG:  finished
writingrun 4 to tape 3: CPU 2.43s/15.20u sec elapsed 20.51 sec</span><br /><span style="font-size: 11pt; font-family:
Arial;color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf order by age,id;</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">LOG:  finished writing run 5 to tape 4: CPU
3.05s/18.96usec elapsed 25.44 sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">STATEMENT: explain analyze select * from big_wf order by age,id;</span><br /><span style="font-size: 11pt;
font-family:Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">LOG:  finished writing run 6 to tape 5: CPU 3.68s/22.74u sec elapsed
30.47sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:
 explainanalyze select * from big_wf order by age,id;</span><br /><span style="font-size: 11pt; font-family: Arial;
color:rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline;">LOG:  finished writing run 7 to tape 6: CPU 4.24s/26.63u sec elapsed 36.61 sec</span><br
/><spanstyle="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight:
normal;font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze select * from
big_wforder by age,id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">LOG: finished writing run 8 to tape 7: CPU 4.78s/30.41u sec elapsed 41.56 sec</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf
orderby age,id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">LOG:
 performsortstarting: CPU 4.81s/30.56u sec elapsed 41.75 sec</span><br /><span style="font-size: 11pt; font-family:
Arial;color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration:
none;vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf order by age,id;</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">LOG:  finished writing run 9 to tape 8: CPU
4.84s/32.07usec elapsed 43.56 sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">STATEMENT: explain analyze select * from big_wf order by age,id;</span><br /><span style="font-size: 11pt;
font-family:Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal;
text-decoration:none; vertical-align: baseline;">LOG:  finished writing final run 10 to tape 9: CPU 4.84s/32.07u sec
elapsed43.56 sec</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:
 explainanalyze select * from big_wf order by age,id;</span><br /><span style="font-size: 11pt; font-family: Arial;
color:rgb(0, 0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline;">LOG:  performsort done (except 10-way final merge): CPU 4.85s/32.16u sec elapsed 43.70
sec</span><br/><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent;
font-weight:normal; font-style: normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze
select* from big_wf order by age,id;</span><br /><span style="font-size: 11pt; font-family: Arial; color: rgb(0, 0, 0);
background-color:transparent; font-weight: normal; font-style: normal; text-decoration: none; vertical-align:
baseline;">LOG: external sort ended, 9006 disk blocks used: CPU 8.60s/41.93u sec elapsed 60.73 sec</span><br /><span
style="font-size:11pt; font-family: Arial; color: rgb(0, 0, 0); background-color: transparent; font-weight: normal;
font-style:normal; text-decoration: none; vertical-align: baseline;">STATEMENT:  explain analyze select * from big_wf
orderby age,id;</span><br />