[RTFACT-14929] Add a new text pattern index for the nodes table for the Quick Search queries Created: 12/Sep/17  Updated: 19/Mar/20

Status: Open
Project: Artifactory Binary Repository
Component/s: Database
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Normal
Reporter: Andrei Komarov Assignee: Yossi Shaul
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
is related to RTFACT-14888 Improve node with properties search b... Resolved
is related to RTFACT-14702 Improve Artifactory quick search quer... Open


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


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)

Generated at Wed Aug 12 01:54:51 UTC 2020 using Jira 8.5.3#805003-sha1:b4933e02eaff29a49114274fe59e1f99d9d963d7.