CREATE TABLE hierarchy_table(id integer PRIMARY KEY,path ltree);
INSERT INTO hierarchy_table
VALUES (1, '1'),
(2,'1.2'),
(3,'1.2.3'),
(4,'1.2.4'),
(5,'1.5'),
(6,'1.5.6'),
(7,'1.5.7'),
(8,'1.5.8');
CREATE INDEX idx_hierarchy_table_gist ON hierarchy_table USING gist(path);
explain analyze select * from hierarchy_table where '1.2' @> path
Result:
Seq Scan on hierarchy_table (cost=0.00..1.10 rows=1 width=36) (actual time=0.009..0.011 rows=3 loops=1)
A table with 8 rows is pointless to index, and pointless to test with.