From: | Bill Moran <wmoran(at)collaborativefusion(dot)com> |
---|---|
To: | Luca Ferrari <fluca1978(at)infinito(dot)it> |
Cc: | "pgsql general" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: help understanding analyze |
Date: | 2006-12-09 02:47:22 |
Message-ID: | 20061208214722.77e78263.wmoran@collaborativefusion.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Luca Ferrari <fluca1978(at)infinito(dot)it> wrote:
>
> Hi all,
> excuse me for this trivial question, but here's my doubt:
> create table person(varchar id, varchar surname, varchar name)
> with id primary key. Now, the query:
> select * from person order by surname,name
> provide me an explaination that is sequential scan + sort, as I expected.
> After that I build an index on surname,name (clustered) and run vacuum to
> update statistics. Then I ran again the query and got the same results (scan
> + sort) with the same time.
> Now my trivial question is: why another sort? The index is clustered so the
> database should not need to sort the output, or am I using wrong the tools?
> Someone can explain me that?
I doubt that the planner has any way to know that the table, at any point
in time, is still 100% clustered. If even one row has been added since
the cluster was done, the table will need resorted.
Might be an optimization that could be done, except that I expect there
will be very few cases where it will actually make a difference. How
often do you have a table that never changes and can always be assured
of being in index order?
-Bill
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-12-09 02:48:26 | Re: help understanding analyze |
Previous Message | Tom Lane | 2006-12-09 02:12:56 | Re: TOAD-like query builder for PostgreSQL? |