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