[DEFECT] Copy Host operation suffers severe performance degradation with large content volume

[dsilvam]

https://helpdesk.dotcms.com/a/tickets/33808

Problem Statement

Under large content value, there is a DB query that takes between 3 to 18 seconds to execute and they are executed in a very high frequency during the copy host operation. Creating some indices on key fields and casts optimizes this execution time to under a second in average.

Steps to Reproduce

• Start a copy host function with the customer database. Ask @dsilvam for the host/site to copy
• Check the logs and see the many occurrences of:

20:53:36.911  WARN  db.DotConnect - Somewhat slow query, total time: 18457.732ms, query preparation time: 0.02848ms, query execution time: 18457.695ms, metadata time: 4.9E-5ms, SQL: SELECT c.inode, cvi.live_inode, c.contentlet_as_json->'fields'->'aliases'->>'value'  AS aliases, clive.contentlet_as_json->'fields'->'aliases'->>'value'  AS live_aliases FROM contentlet c INNER JOIN identifier i ON c.identifier = i.id AND i.asset_subtype = 'Host' INNER JOIN contentlet_version_info cvi ON c.inode = cvi.working_inode LEFT JOIN contentlet clive ON clive.inode = cvi.live_inode  WHERE LOWER(c.contentlet_as_json->'fields'->'aliases'->>'value' ) LIKE ?  OR LOWER(clive.contentlet_as_json->'fields'->'aliases'->>'value' ) LIKE ?

 

Acceptance Criteria

Queries taking way less time to execute. One one of achieving this is creating proper indices to avoid Nested Loop with Sequential Scan.

Indices suggested:

CREATE INDEX idx_contentlet_show_menu_partial 
ON contentlet(show_on_menu) 
WHERE show_on_menu = true;

	
CREATE INDEX idx_contentlet_inode_char_show_menu 
ON contentlet(CAST(inode AS CHAR(36))) 
WHERE show_on_menu = true;


CREATE INDEX idx_cvi_identifier_variant_deleted 
ON contentlet_version_info(identifier, variant_id) 
WHERE deleted = false AND variant_id = 'DEFAULT';

• * Indices created properly on a new database
• * Indices created properly on an existing data, via Upgrade Task**

dotCMS Version

Evergreen 25.10.06-01_57a9470

Severity

High - Major functionality broken

[wezell]

Couple of things - this query could be simplified, especially if we pass in the host structure id (which is just like APILocator.systemHost().getStructureInode()). Also iLike the case insensitive like in postgres.

SELECT 
c.inode, 
c.contentlet_as_json->'fields'->'aliases'->>'value'  AS aliases
FROM contentlet c,
contentlet_version_info cvi
where 
c.structure_inode = ? AND
c.contentlet_as_json->'fields'->'aliases'->>'value' ILIKE 'test.com' AND 
(c.inode = cvi.working_inode or c.inode = cvi.live_inode)