Uploaded image for project: 'Artifactory Binary Repository'
  1. Artifactory Binary Repository
  2. RTFACT-14929

Add a new text pattern index for the nodes table for the Quick Search queries

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Normal
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Database
    • Labels:
      None

      Description

      This was tested specific for PostgreSQL but could apply for additional DBs

      Adding a new text pattern index for the nodes table produced better results when a single & double wildcards are appended to the query below (SELECT...):

      create index idx_name_text on nodes (node_name text_pattern_ops)

      For an existing DB, use the

      concurrently

      statement to avoid locks:

      create index concurrently idx_name_text on nodes (node_name text_pattern_ops)

      Results after adding it:

      postgres=# explain analyze SELECT nodes.node_id, nodes.node_type, nodes.repo, nodes.node_path, nodes.node_name FROM nodes WHERE nodes.node_type=1 AND  nodes.repo  != 'auto-trashcan' AND nodes.node_name LIKE 'a%'
      ;
                                                              QUERY PLAN
      --------------------------------------------------------------------------------------------------------------------------
       Bitmap Heap Scan on nodes  (cost=6.53..731.46 rows=473 width=80) (actual time=0.091..0.668 rows=25 loops=1)
         Filter: (((repo)::text <> 'auto-trashcan'::text) AND ((node_name)::text ~~ 'a%'::text) AND (node_type = 1))
         Rows Removed by Filter: 105
         Heap Blocks: exact=59
         ->  Bitmap Index Scan on idx_name_text  (cost=0.00..6.41 rows=185 width=0) (actual time=0.055..0.055 rows=130 loops=1)
               Index Cond: (((node_name)::text ~>=~ 'a'::text) AND ((node_name)::text ~<~ 'b'::text))
       Planning time: 77.340 ms
       Execution time: 0.707 ms
      (8 rows)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                yossis Yossi Shaul
                Reporter:
                andreik Andrei Komarov
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: