Postgres ltree doesn't use Gist index Why?

Question

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)

http://sqlfiddle.com/#!17/6e363/2

For a tiny table like that, using an index won't make things faster to begin with — user330315
– user330315, Commented Dec 2, 2021 at 19:23
Why would it? It would take longer to do the lookup in the index and use that to find the rows in the table then to just do the Seq Scan. — Adrian Klaver
– Adrian Klaver, Commented Dec 2, 2021 at 19:32

jjanes · Accepted Answer · 2021-12-02 19:26:31Z

1

A table with 8 rows is pointless to index, and pointless to test with.

answered Dec 2, 2021 at 19:26

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

László Tóth Over a year ago

You have right, if I create index before inserts then use index, but after ANALYZE doesn't! Thanks.

László Tóth · Accepted Answer · 2021-12-02 19:14:58Z

0

I have observered, if I have created gist index before inserts it works with index.

But so the recreate index is dangerous :)

answered Dec 2, 2021 at 19:14

László Tóth

6897 silver badges20 bronze badges

Collectives™ on Stack Overflow

Postgres ltree doesn't use Gist index Why?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related