1

Can someone please explain why two indexes mechanism (a btree and a gist) are defined in an example given by Postgresql documentation (Please Check F.21.4. Example section).

This is the example code :

CREATE TABLE test (path ltree);

INSERT INTO test VALUES ('Top');
INSERT INTO test VALUES ('Top.Science');
INSERT INTO test VALUES ('Top.Science.Astronomy');
INSERT INTO test VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO test VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO test VALUES ('Top.Hobbies');
INSERT INTO test VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO test VALUES ('Top.Collections');
INSERT INTO test VALUES ('Top.Collections.Pictures');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO test VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');

CREATE INDEX path_gist_idx ON test USING gist(path);
CREATE INDEX path_idx ON test USING btree(path);

At the last two lines, the author has created two indexes on path column. why? isn't that Gist enough for the purpose?

In section F.21.3 the author implies that b-tree and gist may speed up the following operators :

B-tree index over ltree: <, <=, =, >=, >

GiST index over ltree: <, <=, =, >=, >, @>, <@, @, ~, ?

Which means Gist is enough for all the above operators.let clear my question. Is the author trying to provide example for both indexes here or there is a reason for using both indexes.

I am creating a table with a ltree column and I want to know that should I create both indexes (btree and gist) on my column or gist is just enough?

Iman Nia
  • 2,255
  • 2
  • 15
  • 35

1 Answers1

1

B-tree indices are faster; check this discussion: What's the difference between B-Tree and GiST index methods (in PostgreSQL)?

So the logic behind creating both indices is that GiST is more flexible and can be used when the method of searching isn't supported by B-tree (the extra @>, <@, @, ~, ? that GiST has). However, when you do use the methods common between both (<, <=, =, >=, >), B-tree will be used as it is faster.

Moaaz Assali
  • 147
  • 1
  • 8