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

Hi Marti,

Thanks for your help! I guess I understand what you mean, a clustered index will make sorting as cheap as a seq scan, right?

But what I meant is, is there any potential optimization for the backend implementation? Intuitively, if sorting on one column or two columns will incur the same I/O costs, why should there be so much difference?

Thanks,
Li Jie

----- Original Message -----
From: "Marti Raudsepp" <marti(at)juffo(dot)org>
To: "Jie Li" <jay23jack(at)gmail(dot)com>
Cc: "pgsql-hackers" <pgsql-hackers(at)postgresql(dot)org>
Sent: Thursday, December 23, 2010 10:17 PM
Subject: Re: [HACKERS] Why is sorting on two columns so slower than sorting on one column?

On Thu, Dec 23, 2010 at 09:33, Jie Li <jay23jack(at)gmail(dot)com> wrote:
> While the first sorting takes
> about only 6 seconds, the second one takes over 30 seconds, Is this too
> much than expected? Is there any possible optimization ?

If you're doing these queries often, you should:
CREATE INDEX ix_big_wf_age_id ON big_wf (age, id)

If that's still not fast enough, you can physically sort rows in the
table using the newly created index:
CLUSTER big_wf USING ix_big_wf_age_id;

Please post back your results. :)

Regards,
Marti