Re: Index on wide column

Antonio Sergio de Mello e Souza <asergioz(at)bol(dot)com(dot)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