Cristal clear !
and it have to be the case as my test was done with some not so random data.
but this mean that we cannot put a bunch of datatypes in a PK, as soon as it may be longer than 2701.
I know, its clearly stated in postgres doc about btree," The only limitation is that an index entry cannot exceed approximately one-third of a page (after TOAST compression, if applicable)."
but as this looks quite hard coded, it means that for long utf8 things the data length is not so long before hitting the limit.
is there any plan to adress this ?
kind of index toast ?? :-)
thanks
Marc MILLAS
Senior Architect
+33607850334
On 2021-06-08 18:30:16 +0200, Marc Millas wrote:
> the only pb observed is the size of the object accepted. if the geom is a bit
> "big" then the index errors.about btree size of index object.
> but if I create a table test_l with a text column blabla as a PK, and insert a
> 100 000 character long string , no pb.
> if I do an explain analyze select blabla from test_l order by blabla, postgres
> tells me that it did an index only scan.
> so, I wonder why Postgres is able to put a 100 000 long text in a btree index
> and NOT a geom column which wkt is 10 000 bytes long.
Good question. Maybe your texts compresses better than your geometries:
hjp=> insert into t(t) values(repeat('a', 235327) || '1');
INSERT 0 1
Time: 60.057 ms
hjp=> insert into t(t) values(repeat('a', 235328) || '1');
ERROR: index row size 2720 exceeds maximum 2712 for index "t_pkey1"
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
Time: 58.751 ms
Note the difference between the length of the string I was trying to
insert and the length of the row it complains about.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
