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

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:
Relationship
is related to RTFACT-14888 Improve node with properties search b... Resolved
is related to RTFACT-14702 Improve Artifactory quick search quer... Open

 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)

Generated at Sun Aug 25 23:17:18 UTC 2019 using JIRA 7.6.3#76005-sha1:8a4e38d34af948780dbf52044e7aafb13a7cae58.