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

From Li Jie
Subject Re: Why is sorting on two columns so slower than sorting on one column?
Date
Msg-id 001801cba2ad$cf3b4100$2ad118ac@A0078508
Whole thread Raw
In response to Why is sorting on two columns so slower than sorting on one column?  (Jie Li <jay23jack@gmail.com>)
List pgsql-hackers
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
columnor 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@juffo.org>
To: "Jie Li" <jay23jack@gmail.com>
Cc: "pgsql-hackers" <pgsql-hackers@postgresql.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@gmail.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

pgsql-hackers by date:

Previous
From: "Li Jie"
Date:
Subject: Re: Why is sorting on two columns so slower thansortingon one column?
Next
From: "Li Jie"
Date:
Subject: Re: Why is sorting on two columns so slower than sortingon one column?