I'm trying to load some data into PostgreSQL 8.0.3, and I got the error
message "index row size 2904 exceeds btree maximum, 2713". After a
bunch of searching, I believe that I am getting this error because a
value that I am indexing is longer than ~ 1/3 of the block size - or the
BLCKSZ variable in the src/include/pg_config_manual.h file.

Am I correct so far?

I need to fix this problem. I cannot change the indexed columns. I
cannot shorten the data value. And I cannot MD5 it, or any of those
hashing types of solutions that I saw a lot while searching.

Is there a variable I can set somewhere, so that postgresql would just
truncate the value to the max length that the index can handle when it
goes to enter it into the index, instead of failing with an error? I
would be fine with not having this particular row fully indexed, so long
as I could still retrieve the full data value.

The other solution that I saw was to modify the BLCKSZ variable. From
what I saw, it appears that to change that variable, I would need to
dump my databases out, recompile everything, and then reload them from
scratch. Is this correct?

Currently the BLCKSZ variable is set to 8192. What are the
performance/disk usage/other? implications of doubling this value, to 16384?

Any other suggestions in dealing with this problem?

Thanks,

Dan

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Mon, Jul 18, 2005 at 14:44:26 -0500,
Dan Armbrust <daniel.armbrust.list@gmail.com> wrote:
> I'm trying to load some data into PostgreSQL 8.0.3, and I got the error
> message "index row size 2904 exceeds btree maximum, 2713". After a
> bunch of searching, I believe that I am getting this error because a
> value that I am indexing is longer than ~ 1/3 of the block size - or the
> BLCKSZ variable in the src/include/pg_config_manual.h file.
>
> Am I correct so far?
>
> I need to fix this problem. I cannot change the indexed columns. I
> cannot shorten the data value. And I cannot MD5 it, or any of those
> hashing types of solutions that I saw a lot while searching.

Can you explain how you are using the index now and what you are trying
to accomplish? It is hard to suggest alternatives without knowing what
you are really trying to do.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> I'm trying to load some data into PostgreSQL 8.0.3, and I got the error
> message "index row size 2904 exceeds btree maximum, 2713". After a
> bunch of searching, I believe that I am getting this error because a
> value that I am indexing is longer than ~ 1/3 of the block size - or the
> BLCKSZ variable in the src/include/pg_config_manual.h file.
>
> Am I correct so far?
>
> I need to fix this problem. I cannot change the indexed columns.
>

can you drop the index and retry?
what typo is the column?



--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> The index that is failing is CREATE INDEX i1 ON conceptproperty USING btree
> (codingschemename, property, propertyvalue).
>

I don't think you could create indexes on text fields... there are
other type of indexes for that... tsearch2 for example

--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Dan Armbrust <daniel.armbrust.list@gmail.com> writes:
> The index that is failing is CREATE INDEX i1 ON conceptproperty USING
> btree (codingschemename, property, propertyvalue).<br>
> <br>
> Usually, the 'propertyValue' field is fairly short - 100 chars or
> less.&nbsp; And in those cases, I need to be able to do an indexed search on
> it.

You do realize that the above index has nothing whatever to do with
searches on propertyvalue? (Or at least not propertyvalue alone.)

My advice is to drop propertyvalue from the index and see what
performance you get.

You could possibly do something with a partial index, eg
create index ... where length(propertyvalue) < 2000
but that'd probably require modifying your queries, so I suspect it's
not really very practical.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Mon, 2005-07-18 at 15:17, Dan Armbrust wrote:
> We have built a Model for terminologies that we call The Lexical Grid
> (more info http://informatics.mayo.edu/LexGrid/index.php)
>
> LexGrid has multiple backend data storage mechanisms, including LDAP
> and SQL. We do our best to remain implementation independent - our
> SQL implementations, for example can run against MS Access, DB2, MySQL
> and PostgreSQL.
>
> I'm currently trying to load a new terminology into a PosgreSQL
> backend, and arrived at this error because it happens to have a couple
> of very large data values that get mapped into the 'propertyvalue'
> field.

Well, if you're trying to maintain compatibility to multiple backends,
then requiring a non-standard block size is going to be a big
non-starter for most folks running postgresql. Very few, if any users,
are going to be willing to install a version of postgresql setup that
way just because of one program's needs.

Does this need to be a unique index for any reason? If so, then an
md5(propertyvalue) would give you that. If not, then you could make a
partial index like so:

create index yada_dx on yada (propertyvalue) where
length(propertybalue)<100;

But that would require you to add "where length(propertyvalue)<100" onto
your queries to use the index, I believe.

OTOH, this may be the time to use a hash index.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Mon, 2005-07-18 at 16:01, Dan Armbrust wrote:
> Hmm, well, I don't know if it is actually building an index properly
> on this column, I just assumed that it was. It doesn't fail on every
> insert, only on the one that has a really long text value. I know it
> doesn't use the index when I do "ILIKE" queries, resulting in poor
> performance... but I assumed that was because I was trying to do a
> case insensitve search on a case sensitive column index. I didn't
> want to go down the road of writing even more database implementation
> specific code. I will usually be using Lucene for the full text
> searches anyway.
>
> Where is the documentation on tsearch2? I haven't seen it mentioned
> anywhere except a couple of mailing list postings.
>
> All of my other limitations on changing things aside - given a query
> like this:
>
> Select * from conceptproperty where codingSchemeName='foo' AND
> property='anotherfoo' and propertyValue ILIKE 'valu%'

OK, how selective are each of these three fields? If codingSchemeName
isn't very selective, then you'll likely never win with an index. Put
the most selective item first, that way the database will have the
smallest set of data to have to play with to get the answer you asked
for.

Next, hash indexes might have been the answer, when I didn't know you
were using like. Hash indexes, at least on my 7.4 box, can't use like.

Ilike can't use indexes period, and it's better to create a functional
index:

create index indexname on table (lower(fieldname));

and then always search on lower(fieldname);

> What indexe(s) would be recommended?
>
> My current 3 column index (that works on other DB's) doesn't perform
> well due to case sensitivity issues, and now fails, due to data length
> issues.

The reason your three column index likely doesn't work well in
postgresql is that you probably don't have much selectivity in the first
column. I'm just guessing there aren't that many coding schemes, but
there probably are plenty of properties. So, setting up your index with
that value first, and in first in your where clause should help,
assuming it's more selective.

IF you build a testcase on propertvalue, and try ilike, you'll notice
that it, quite simply does NOT use indexes.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Dan Armbrust <daniel.armbrust.list@gmail.com> writes:
> All of my other limitations on changing things aside - given a query
> like this:
>
> Select * from conceptproperty where codingSchemeName='foo' AND
> property='anotherfoo' and propertyValue ILIKE 'valu%'
>
> What indexe(s) would be recommended?

I'd index on codingSchemeName and property and not worry so much about
propertyValue. I rather doubt that that part of the query is adding a
performance-critical amount of selectivity --- and even if you could fit
propertyValue into the index, you'd have to jump through hoops to get a
case-insensitive search on it when the other columns are case-sensitive.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Dan Armbrust wrote:
> Hmm, well, I don't know if it is actually building an index properly on
> this column, I just assumed that it was. It doesn't fail on every
> insert, only on the one that has a really long text value. I know it
> doesn't use the index when I do "ILIKE" queries, resulting in poor
> performance... but I assumed that was because I was trying to do a case
> insensitve search on a case sensitive column index. I didn't want to go
> down the road of writing even more database implementation specific
> code. I will usually be using Lucene for the full text searches anyway.
>
> Where is the documentation on tsearch2? I haven't seen it mentioned
> anywhere except a couple of mailing list postings.
>

http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/

And a devx article here:

http://www.devx.com/opensource/Article/21674/0


> All of my other limitations on changing things aside - given a query
> like this:
>
> Select * from conceptproperty where codingSchemeName='foo' AND
> property='anotherfoo' and propertyValue ILIKE 'valu%'
>
> What indexe(s) would be recommended?

Why don't you do this:

Select * from conceptproperty where codingSchemeName='foo' AND
property='anotherfoo' and propertyValue ~ lower('valu');

and have an index:

create index lower_propertyvalue_idx on
conceptproperty(lower(propertyvalue));

and have a index:

(codingschemename,property)

I would also be curious to see an explain analyze.

Sincerely,

Joshua D. Drake



--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Mon, 18 Jul 2005, Jaime Casanova wrote:

> I don't think you could create indexes on text fields... there are
> other type of indexes for that... tsearch2 for example

You can index text fields, but you can't insert values bigger then
BLOCKSIZE/3 when you have an index on that column.

--
/Dennis Björklund


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend