Get all ltree nodes at depth

I have an ltree column containing a tree with a depth of 3. I'm trying to write a query that can select all children at a specific depth (level 1 = get all parents, 2 = get all children, 3 = get all grandchildren). I know this is pretty straightforward with n_level:

SELECT path FROM hierarchies
WHERE
    nlevel(path) = 1
LIMIT 1000;

I have 200,000 dummy records and it's pretty fast (~170 ms). However, this query uses a sequential scan. I think it'd be better to write it in a way that takes advantage of the ltree operators supported by the GiST index. Frustratingly, I can't seem to wrap my brain around them, and I haven't found a similar question on SO or DBA (besides this one on finding leaves)

Any advice is appreciated!

Solution 1:

The only index that could support your query is a simple b-tree index on an expression.

create index on hierarchies((nlevel(path)))

Note however that it is quite possible for the planner to choose a sequential scan anyway, exemplary in the case the number of rows with level 1 is much more than other levels.