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

<font size="2">Hi,<br /><br />Here is the test table, <br /><br />postgres=# \d big_wf <br />    Table
"public.big_wf"<br/> Column |  Type   | Modifiers <br />--------+---------+-----------<br /> age    | integer | <br
/> id    | integer | <br /><br />postgres=# \dt+ big_wf <br />                     List of relations<br /> Schema | 
Name | Type  |  Owner   |  Size  | Description <br />--------+--------+-------+----------+--------+-------------<br
/> public| big_wf | table | workshop | 142 MB | <br /><br /><br />The first query sorting on one column:<br
/>postgres=#explain analyze select * from big_wf order by age;<br
/>                                                      QUERY
PLAN                                                       <br />
-------------------------------------------------------------------------------------------------------------------------<br
/> Sort (cost=565525.45..575775.45 rows=4100000 width=8) (actual time=11228.155..16427.149 rows=4100000 loops=1)<br />
  Sort Key: age<br />   Sort Method:  external sort  Disk: 72112kB<br />   ->  Seq Scan on big_wf 
(cost=0.00..59142.00rows=4100000 width=8) (actual time=6.196..4797.620 rows=4100000 loops=1)<br /> Total runtime:
19530.452ms<br /> (5 rows)<br /><br />The second query sorting on two columns:<br />postgres=# explain analyze select *
frombig_wf order by age,id;<br />                                                       QUERY
PLAN                                                       <br />
-------------------------------------------------------------------------------------------------------------------------<br
/> Sort (cost=565525.45..575775.45 rows=4100000 width=8) (actual time=37544.779..48206.702 rows=4100000 loops=1)<br />
  Sort Key: age, id<br />   Sort Method:  external merge  Disk: 72048kB<br />   ->  Seq Scan on big_wf 
(cost=0.00..59142.00rows=4100000 width=8) (actual time=6.796..5518.663 rows=4100000 loops=1)<br /> Total runtime:
51258.000ms<br /> (5 rows)<br /><br />The verision is 9.0.1 and the work_mem is 20MB. One special thing is, the first
column(age)of all the tuples are of the same value, so the second column(id)<span
id="internal-source-marker_0.431774702563559"style="font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;"></span> is
alwaysneeded for comparison.<span id="internal-source-marker_0.431774702563559" style="font-family: Arial; color:
rgb(0,0, 0); background-color: transparent; font-weight: normal; font-style: normal; text-decoration: none;
vertical-align:baseline;">  While the first sorting takes about only 6 seconds, the second one takes over 30 seconds, 
Isthis too much than expected? Is there any </span></font><font size="2"><span
id="internal-source-marker_0.431774702563559"style="font-family: Arial; color: rgb(0, 0, 0); background-color:
transparent;font-weight: normal; font-style: normal; text-decoration: none; vertical-align: baseline;">possible
</span></font><spanid="internal-source-marker_0.431774702563559" 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;"><font size="2">optimization ?</font><br /><br />Thanks,<br />Li Jie<br /></span>