Why is sorting on two columns so slower than sorting on one column?

Поиск
Список
Период
Сортировка
От Jie Li
Тема Why is sorting on two columns so slower than sorting on one column?
Дата
Msg-id AANLkTi=VK+kLjEvVdEL7y9P-rPE3OJVzqruR59EPmkj+@mail.gmail.com
обсуждение исходный текст
Ответы Re: Why is sorting on two columns so slower than sorting on one column?  (Kenneth Marshall <ktm@rice.edu>)
Re: Why is sorting on two columns so slower than sorting on one column?  (Marti Raudsepp <marti@juffo.org>)
Список 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> 

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Jan Urbański
Дата:
Сообщение: pl/python SPI in subtransactions
Следующее
От: Jan Urbański
Дата:
Сообщение: pl/python invalidate functions with composite arguments