B-tree index with sorting question
In the docs at http://www.postgresql.org/docs/9.0/static/sql-createindex.html,
I see that you can build indexes that include ordering.
Eg. create index t_col_idx on t (col DESC NULLS LAST);
Does that mean that the initial creation of the index acts like the
CLUSTER command? If so, what happens to subsequent inserts/updates of
the indexed attribute? Are those values then indexed in the order that
they are inserted -- which could be contrary to the ordering specified
in the index creation?
bricklen <bricklen@gmail.com> writes:
In the docs at http://www.postgresql.org/docs/9.0/static/sql-createindex.html,
I see that you can build indexes that include ordering.
Eg. create index t_col_idx on t (col DESC NULLS LAST);
Does that mean that the initial creation of the index acts like the
CLUSTER command?
No, it just changes the order that the index keeps its entries in.
regards, tom lane
On 2010-12-28, at 5:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
bricklen <bricklen@gmail.com> writes:
In the docs at http://www.postgresql.org/docs/9.0/static/sql-createindex.html,
I see that you can build indexes that include ordering.
Eg. create index t_col_idx on t (col DESC NULLS LAST);Does that mean that the initial creation of the index acts like the
CLUSTER command?No, it just changes the order that the index keeps its entries in.
regards, tom lane
And that holds true for all subsequents inserts too?
Le 29/12/2010 05:28, Bricklen a �crit :
On 2010-12-28, at 5:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
bricklen <bricklen@gmail.com> writes:
In the docs at http://www.postgresql.org/docs/9.0/static/sql-createindex.html,
I see that you can build indexes that include ordering.
Eg. create index t_col_idx on t (col DESC NULLS LAST);Does that mean that the initial creation of the index acts like the
CLUSTER command?No, it just changes the order that the index keeps its entries in.
regards, tom lane
And that holds true for all subsequents inserts too?
Yes, but still only the index entries.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
On Tue, Dec 28, 2010 at 11:04 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
Le 29/12/2010 05:28, Bricklen a écrit :
On 2010-12-28, at 5:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
bricklen <bricklen@gmail.com> writes:
In the docs at http://www.postgresql.org/docs/9.0/static/sql-createindex.html,
I see that you can build indexes that include ordering.
Eg. create index t_col_idx on t (col DESC NULLS LAST);Does that mean that the initial creation of the index acts like the
CLUSTER command?No, it just changes the order that the index keeps its entries in.
regards, tom lane
And that holds true for all subsequents inserts too?
Yes, but still only the index entries.
Great, thanks for the clarifications!