optimize sql query causing slowness on high-traffic sites

[yolabingo]

Parent Issue

No response

Problem Statement

@wezell Identified this query which causes slow application responses and strains database server resources on a high-traffic site.

The code is just trying to figure out if any other pages use a template - but it is running through all content, old, new, files, blogs, videos, pages, widgets when it just should look at pages.

dotConnect.setSQL("SELECT identifier,inode,language_id,variant_id as variant "
	+ "FROM contentlet "
	+ "WHERE contentlet_as_json->'fields'->'template'->>'value' =  ?");
}

TemplateFactoryImpl.java#L738-L746

Steps to Reproduce

unknown

Acceptance Criteria

Improve query time to run.

Let's QA editing a page layout to check that:

• It creates a new template/layout if another page uses the same template
• Modifies the base template if this is the only page with that template.

dotCMS Version

all current versions

Proposed Objective

Application Performance

Proposed Priority

Priority 2 - Important

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

https://dotcms.slack.com/archives/C06TM536N9J/p1723045162451829

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

No response

[erickgonzalez]

Here are some results using a customer DB:

Original:

explain analyze SELECT identifier,inode,language_id,variant_id as variant FROM contentlet WHERE contentlet_as_json->'fields'->'template'->>'value' =  '967647fc23e382aeed8f3a440046fe24';
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on contentlet  (cost=0.00..51520.36 rows=661 width=86) (actual time=39.873..305.649 rows=181 loops=1)
   Filter: ((((contentlet_as_json -> 'fields'::text) -> 'template'::text) ->> 'value'::text) = '967647fc23e382aeed8f3a440046fe24'::text)
   Rows Removed by Filter: 131947
 Planning Time: 0.068 ms
 Execution Time: 305.686 ms
(5 rows)

After doing a couple of optimizations on the query (only pull the identifier and the variant and filter by structure_inode):

explain analyze SELECT c.identifier, c.variant_id as variant FROM contentlet c WHERE EXISTS (SELECT 1 FROM structure s WHERE s.inode = c.structure_inode AND s.structuretype = '5') AND c.contentlet_as_json->'fields'->'template'->>'value' = '967647fc23e382aeed8f3a440046fe24';
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=13.69..10099.92 rows=12 width=41) (actual time=2.805..4.231 rows=181 loops=1)
   ->  Seq Scan on structure s  (cost=0.00..8.71 rows=4 width=33) (actual time=0.012..0.058 rows=4 loops=1)
         Filter: (structuretype = 5)
         Rows Removed by Filter: 213
   ->  Bitmap Heap Scan on contentlet c  (cost=13.69..2522.76 rows=4 width=74) (actual time=0.692..1.036 rows=45 loops=4)
         Recheck Cond: ((structure_inode)::text = (s.inode)::text)
         Filter: ((((contentlet_as_json -> 'fields'::text) -> 'template'::text) ->> 'value'::text) = '967647fc23e382aeed8f3a440046fe24'::text)
         Rows Removed by Filter: 422
         Heap Blocks: exact=1310
         ->  Bitmap Index Scan on idx_contentlet_4  (cost=0.00..13.69 rows=703 width=0) (actual time=0.034..0.034 rows=468 loops=4)
               Index Cond: ((structure_inode)::text = (s.inode)::text)
 Planning Time: 0.336 ms
 Execution Time: 4.275 ms
(13 rows)

It can be optimized even more by adding a new index but don't know if is worth it?

CREATE INDEX idx_contentlet_template_value 
ON contentlet ((contentlet_as_json->'fields'->'template'->>'value'));

[wezell]

What does it look like with the index on the field? I am asking because the cost=13.69..10099.92 are the lowest and highest costs that can be estimated by the planner in different cases. both of those numbers should be as low as possible. I am trying the explain on a large scale local env and I am seeing this:

# explain analyze SELECT c.identifier, c.variant_id as variant FROM contentlet c WHERE EXISTS (SELECT 1 FROM structure s WHERE s.inode = c.structure_inode AND s.structuretype = '5') AND c.contentlet_as_json->'fields'->'template'->>'value' = '967647fc23e382aeed8f3a440046fe24';
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=264.55..487088.55 rows=195 width=48) (actual time=23247.043..23247.044 rows=0 loops=1)
   ->  Seq Scan on structure s  (cost=0.00..25.59 rows=6 width=36) (actual time=806.883..808.871 rows=6 loops=1)
         Filter: (structuretype = 5)
         Rows Removed by Filter: 681
   ->  Bitmap Heap Scan on contentlet c  (cost=264.55..81175.98 rows=118 width=85) (actual time=3739.692..3739.692 rows=0 loops=6)
         Recheck Cond: ((structure_inode)::text = (s.inode)::text)
         Filter: ((((contentlet_as_json -> 'fields'::text) -> 'template'::text) ->> 'value'::text) = '967647fc23e382aeed8f3a440046fe24'::text)
         Rows Removed by Filter: 759
         Heap Blocks: exact=3783
         ->  Bitmap Index Scan on idx_contentlet_4  (cost=0.00..264.52 rows=23656 width=0) (actual time=15.838..15.838 rows=759 loops=6)
               Index Cond: ((structure_inode)::text = (s.inode)::text)
 Planning Time: 539.620 ms
 JIT:
   Functions: 10
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 14.376 ms, Inlining 0.000 ms, Optimization 43.586 ms, Emission 753.404 ms, Total 811.366 ms
 Execution Time: 31549.778 ms
(17 rows)

[erickgonzalez]

@wezell with the index the execution time stays under 1 ms, and the costs are constant.

explain analyze SELECT c.identifier, c.variant_id as variant FROM contentlet c WHERE EXISTS (SELECT 1 FROM structure s WHERE s.inode = c.structure_inode AND s.structuretype = '5') AND c.contentlet_as_json->'fields'->'template'->>'value' = '967647fc23e382aeed8f3a440046fe24';
                                                                                              QUERY PLAN                                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=23.36..166.24 rows=12 width=41) (actual time=0.237..0.597 rows=181 loops=1)
   ->  Seq Scan on structure s  (cost=0.00..8.71 rows=4 width=33) (actual time=0.011..0.051 rows=4 loops=1)
         Filter: (structuretype = 5)
         Rows Removed by Filter: 213
   ->  Bitmap Heap Scan on contentlet c  (cost=23.36..39.34 rows=4 width=74) (actual time=0.052..0.131 rows=45 loops=4)
         Recheck Cond: (((((contentlet_as_json -> 'fields'::text) -> 'template'::text) ->> 'value'::text) = '967647fc23e382aeed8f3a440046fe24'::text) AND ((structure_inode)::text = (s.inode)::text))
         Heap Blocks: exact=143
         ->  BitmapAnd  (cost=23.36..23.36 rows=4 width=0) (actual time=0.047..0.047 rows=0 loops=4)
               ->  Bitmap Index Scan on idx_contentlet_template_value  (cost=0.00..9.25 rows=661 width=0) (actual time=0.010..0.010 rows=181 loops=4)
                     Index Cond: ((((contentlet_as_json -> 'fields'::text) -> 'template'::text) ->> 'value'::text) = '967647fc23e382aeed8f3a440046fe24'::text)
               ->  Bitmap Index Scan on idx_contentlet_4  (cost=0.00..13.69 rows=703 width=0) (actual time=0.031..0.031 rows=468 loops=4)
                     Index Cond: ((structure_inode)::text = (s.inode)::text)
 Planning Time: 0.338 ms
 Execution Time: 0.631 ms
(14 rows)

[wezell]

This is better though if we are going to write a startup task we need to test how long it takes it against a customer with a large dataset as creating an index locks the table.

[bryanboza]

we need some work here:

• waiting for this fix in order to retest: https://github.com/dotCMS/core/pull/30354/files

[bryanboza]

Fixed tested doing an upgrade from 24.10_LTS to main.