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?