  Hi all,

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.

Please, are there any restrictions about using such a wide column to
order a table? Can an index on that column help?

I'm running PostgreSQL 7.1.2, on Linux 2.2.16, compiled with options:
--prefix=/usr/local/pgsql --enable-locale --enable-multibyte


Regards,

Antonio Sergio


Wed, 24 Oct 2001 11:51:53 -0400
Antonio Sergio de Mello e Souza <asergioz@bol.com.br>
Index on wide column
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

Wed, 24 Oct 2001 14:29:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Index on wide column 