Skip to content
Snippets Groups Projects

Do not filter namespaces by type

    • Open Issue created by Alex Pooley @alexpooley

    A query on a Group model results in a filter of type = 'Group' on the namespaces table.

    @ahegyi has found that this filtering accounts for 50% of the recursive CTE querying time. In our specific example case, this was 1.5ms of a 3ms query.

    For a linear search using the traversal_ids system the type = 'Group' filter accounted for 99% of the query time!

    From the PostgreSQL docs:

    Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway, there is no point in keeping those rows in the index at all.

    Walking a namespace hierarchy with a filter of type = 'Group' on the namespaces table is redundant. All hierarchies are homogeneous such that a search from a namespace to a parent or child namespace will result in a namespace of the same type. In other words, a group's parent will be type = 'Group', and its children will also be type = 'Group'.

    There are two potential solutions that I see.

    1. Rework queries to operate on the Namespace object.
    2. Create a groups table to back the Group model, rather than rely on STI.

    With the introduction of the traversal_ids system I will be able to make those queries skip the type = 'Group' filter. This will replace our core use of recursive CTE queries.

    There may be more queries out there not covered by the traversal_ids MR that will still filter by type = 'Group' though.

    This inefficiency may also exist in other places where we use STI.

    I am creating this issue as a placeholder/reminder of this problem.

    Edited by Alex Pooley

    Designs

      Child items ...

      2. Activity

      Please register or sign in to reply