Re: Why is sorting on two columns so slower thansortingon one column?

On Fri, 2010-12-24 at 00:27 -0500, Jie Li wrote:

>         I doubt the cost of comparing two integers is the issue here;
>         rather
>         it's more likely one of how many merge passes were needed.
>          You could
>         find out instead of just speculating by turning on trace_sort
>         and
>         comparing the log outputs.

> postgres=# explain analyze select * from big_wf order by id;

> LOG:  switching to external sort with 74 tapes: CPU 0.29s/0.28u sec
> elapsed 0.71 sec
> LOG:  external sort ended, 9006 disk blocks used: CPU 8.01s/27.02u
> sec 

> elapsed 42.92 sec
> STATEMENT:  explain analyze select * from big_wf order by id;


> STATEMENT:  explain analyze select * from big_wf order by age,id;

> LOG:  begin tuple sort: nkeys = 2, workMem = 20480, randomAccess = f
> STATEMENT:  explain analyze select * from big_wf order by age,id;
> LOG:  switching to external sort with 74 tapes: CPU 0.28s/0.30u sec
> elapsed 0.67 sec
> LOG:  external sort ended, 9006 disk blocks used: CPU 8.60s/41.93u sec
> elapsed 60.73 sec
> STATEMENT:  explain analyze select * from big_wf order by age,id;

I think the answer is that only the first column comparison is
optimised. Second and subsequent comparisons are not optimised.

-- Simon Riggs           http://www.2ndQuadrant.com/books/PostgreSQL Development, 24x7 Support, Training and Services