Antonio Sergio de Mello e Souza <asergioz@bol.com.br> writes:
> I need to perform a tree traversal on a big table (millions of rows).
> To avoid recursive queries, one for each non-leaf node, this table has,
> in addition to its 70 columns, a VARCHAR(30000) column that is used
> exclusively to sort the rows with the required order. The actual content
> length in that column is expected to be, on average, much less than the
> declared limit and the text will be composed of digits and letters only.
Are there any entries that will actually approach 30000 chars?
> Please, are there any restrictions about using such a wide column to
> order a table?
No.
> Can an index on that column help?
btree indexes can't cope with index entries wider than 1/3 page, so
you'd probably find that building a btree index fails, if there really
are 30k-wide entries in the column. This limit is squishy because the
entries can be TOAST-compressed, but you're not likely to get 12:1
compression. You could improve matters by increasing BLXKSZ to 32K,
however; then you'd only need 3:1 compression, which might work
depending on how repetitive the column data is.
regards, tom lane