Thread: 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!