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