2

Let's have a table:

usr terms
usr1 [v1,v2]
usr1 [v3]
usr1 [v1]
usr2 [v1]
usr2 [v2]

and assume we create a multi-column GIN index on it.

CREATE INDEX my_gin_ix ON items USING gin (usr,terms);

What will the B-Tree of keys look like?

From the docs (and postgres repo) I see that each B-Tree node should have a column identifier and a key extracted from the field (each field can result in multiple keys), but I can't find any example that would clarify whether the individual columns of the same row end up being "subtrees" of each other or whether they are indexed as independent values.

In other words if I run the query usr = 'usr1' AND terms @> array['v1'] will the DB:

a. find usr1 and then in its subtree find a node with v1 which will yield a single posting list or

b. find usr1 and the associated posting list and then separately search for v1 and fetch the associated posting list and then compute the intersection of the two posting lists?

Improve this question

1 Answer 1

Reset to default
3

It does your thing labelled "b". It will do this even for the a one column index, or a two column index in which only one column is used. For example terms @> array['v1','v2'] will also consult two parts of the index and AND the results. This is essentially a BitmapAnd operation, but it not explicitly displayed as such in the query plan as it is hidden behind the GIN machinery.

Improve this answer
4
  • out of curiosity, does it mean that in the B-Tree all the keys generated from one column come first and only then come all the keys from the second column? I imagine you can't just compare for order of two values of two arbitrary types.
    – fghibellini
    May 31 at 16:42
  • Bitmap operations return the results in physical table order.
    – Laurenz Albe
    May 31 at 19:26
  • 1
    @fghibellini As I understand it, the data is represented as if it were something like the compound value (1,"v1"), where the 1 indicates this if for the 1st column in the index. So all the value are segregated by which column they belong to. But there is no reason you couldn't compare the order of different types. The results might not make sense, but they don't need to make sense they only need to be consistent.
    – jjanes
    May 31 at 22:42
  • got it. thanks a lot 👍
    – fghibellini
    Jun 1 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.