Re: index unique - Mailing list pgsql-general

From Marc Millas
Subject Re: index unique
Date
Msg-id CADX_1aagcNrVGyr6up_pRGdRJBJtQxcMAT=9PKkuRKnBG0BpxA@mail.gmail.com
Whole thread Raw
In response to Re: index unique  ("Peter J. Holzer" <hjp-pgsql@hjp.at>)
Responses Re: index unique
Re: index unique
List pgsql-general
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 Tue, Jun 8, 2021 at 8:42 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
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!"

pgsql-general by date:

Previous
From: "Peter J. Holzer"
Date:
Subject: Re: index unique
Next
From: "David G. Johnston"
Date:
Subject: Re: index unique