Re: Covering GiST indexes

From: Andreas Karlsson <andreas(at)proxel(dot)se>
To: Andrey Borodin <x4mmm(at)yandex-team(dot)ru>
Cc: Dmitry Dolgov <9erthalion6(at)gmail(dot)com>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Teodor Sigaev <teodor(at)sigaev(dot)ru>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Covering GiST indexes
Date: 2019-01-29 02:32:34
Message-ID: faaf7834-b057-a76b-a5d0-36db41cda26f@proxel.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On 1/28/19 7:26 PM, Andrey Borodin wrote:
>> * I am no fan of the tupdesc vs truncTupdesc separation and think that it is a potential hazard, but I do not have any better suggestion right now.
> B-tree is copying tupdesc every time they truncate tuple. We need tuple truncation a little more often: when we are doing page split, we have to form all page tuples, truncated.
> Initially, I've optimized only this case, but this led to prepared tupledesc for truncated tuples.
>>
>> * There is no test case for exclusion constraints, and I feel since that is one of the more important uses we should probably have at least one such test case.
>
> Actually, I did not understand this test case. Can you elaborate more on this? How included attributes should participate in exclude index? What for?

I mean include a table like below among the tests. I feel like this is a
main use case for INCLUDE.

CREATE TABLE t2 (
x int4range,
y int,

EXCLUDE USING gist (x WITH &&) INCLUDE (y)
);
>> * Why the random noise in the diff below? I think using "(c3) INCLUDE (c4)" for both gist and rtree results in a cleaner patch.
> I've used columns with and without opclass in INCLUDE. This led to these seemingly random changes.

I mean the diff would be smaller as the below. It also may make sense to
make both lines "(c3) INCLUDE (c1, c4)".

CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box);
CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4);
CREATE INDEX on tbl USING gist(c3) INCLUDE (c4);
CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4);
CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4);
CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4);
-CREATE INDEX on tbl USING rtree(c1, c2) INCLUDE (c3, c4);
+CREATE INDEX on tbl USING rtree(c3) INCLUDE (c4);
CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4);

Andreas

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2019-01-29 02:35:26 Re: A few new options for vacuumdb
Previous Message Tom Lane 2019-01-29 02:28:48 Re: jsonpath