Property Index in AEM - Index properties and steps to create custom index

Property Index is for queries involving property constraints - equality, exist/not null check on the property.
Oak property Index:

• The node of type "oak:QueryIndexDefinition" with 

Mandatory Properties
Name	Type	Value
type	String	property
propertyNames	Name[]	list of properties for which the index needs to be created. Example: jcr:title, cq:lastModified, hideInNav property for a page content cq:ParentPath property for an AEM Asset
Optional/Supporting Properties
declaringNodeTypes	Name[]	list of node types for which the index is applicable for Example: cq:PageContent rep:Authorizable
unique	Boolean	true  Applicable for queries with a unique constraint This property should be used along with declaringNodeTypes Example: /oak:index/authorizableId
includedPaths	String[]	paths that are included in this index. If not set, it is "/" For queries with path restrictions that are not excluded and part of included paths Example: Scenario 1: If two properties are used together. (as highlighted in red) The index will be picked if query has "path" predicate to be "/content/dam/we-retail/en/people"  will not be picked if query has "path" predicate to be "/content/dam/we-retail/en/products" a query has "path" predicate to be "/content/dam/we-retail/en" (this path is available as part of excluded paths) Scenario 2: If includedPaths property alone is mentioned. Then The index will be picked if query has "path" predicate to be "/content/dam/we-retail/en" or  anything under  "/content/dam/we-retail/en" (/en/.*) heirarchy. will not be picked if query has "path" predicate to be "/content/dam/we-retail"
excludedPaths	String[]	paths that are excluded in this index. If not set, it is "none" For queries with path restrictions that are not excluded and part of included paths Example: Scenario 1: Same as above Scenario 2: In the same example snap above, If excludedPaths property alone is mentioned The index will not be picked if query has "path" predicate to be "/content/dam/we-retail/en/products" will be picked if query has "path" predicate to be "/content/dam/we-retail/en/.*"
valuePattern	String	Regex of all indexed values. The index will be used when the property value matches the pattern Example: If a property value is all lowercase alphabets, then valuePattern -> [a-z]+ The index will be picked if  the query has a "property.value" predicate matching this pattern.
valueExcludedPrefixes	String[]	The prefix of property values to be excluded The index will be picked if the property value does not start with the given prefix  Example: The index will be picked if  the query has a "property.value" predicate that doesn't start with the prefix Use case: When cq:tag is tagged to page/asset, the value will be namespace:tagid Index definition can then restricted for a specific namespace value using this valueExcluded/valueIncludedPrefixes
valueIncludedPrefixes	String[]	The prefix of property values to be included The index will be picked if the property value starts with the given prefix  Used for equality, like conditions. Example: The index will be picked if  a query has a "property.value" predicate that starts with the prefix
entryCount	Long	estimated number of path entries  This is related to cost estimation. high entry count -> high cost
keyCount	Long	estimated number of key entries Again related to cost estimation. It is inversely proportional here. Low key count -> high cost; high key count -> low cost.
useIfExists	String	Useful in blue-green deployments, when using Composite Node Store (Since Oak version 1.10.0) In AEM, it is 6.5 version which has Oak version to be 1.10.2
Property to support async reindexing
reindex-async	Boolean	true (Asynchronous Reindexing is explained in detail below with video demo)
Properties that gets created automatically
reindex	Boolean	false
reindexCount	Long	1, the very first time when the property index is created. the number gets incremented by 1 every time reindex is triggered.
async	String	async-reindex  (Related to asynchronous reindexing, explained below)

Steps to create custom property index:
Sample query involving property constraint - Get all live copy pages which have overwritten the properties/which has cq:propertyInheritanceCancelled

• path=/content/we-retail
• type=cq:Page
• 1_property=@jcr:content/cq:propertyInheritanceCancelled
• 1_property.operation=exists
• p.limit=-1
• "Explain Query" response for the above query :

• Now is the time to create a Property Index for the property - cq:propertyInheritanceCancelled
• Navigate to CRXDE -> /oak:index node
• Being on oak:index node, Create a new node of 
• name  -> cqPropertyInheritanceCancelled (in par with the naming convention of OOB indexes) 
• type  -> oak:QueryIndexDefinition
• Add below mandatory properties

• Name	Type	Value
  type	String	property
  propertyNames	Name[]	cq:propertyInheritanceCancelled

• Behind the scene: Property Index works in Synchronous mode of Indexing, the moment index definition is persisted, it will start indexing the related content. 
• On "Refresh" of newly created index node  -> below two properties are reflected automatically

Name	Type	Value
reindex	Boolean	false
reindexCount	Long	1

• Note: 
• Refresh to reflect the properties that are automatically created. 
• New property index node along with two mandatory properties to be persisted in one shot.
• 
  
• Execute the sample query again and observe the index used: 

• Observation/Conclusion:
• For a query involving property constraint, if both property and lucene index is available -> In general, Property Index will be picked. (Cost value is low for property index compared to lucene index as evident from the above screenshot for this use case)
• Initially when we executed the query without property index, lucene index related to the type(cq:Page) we used in the query is picked which is "cqPageLucene"

With this, we are done with creating a Property Index for a specific property with mandatory properties.

Reindexing Property Index:

One of the possible causes for reindexing is, change in the existing index definition. Reindexing can be done in two ways for a Property Index.

• Synchronous way
• By updating reindex property to true. 
• Once it is finished indexing, it will automatically set it back to false with an update to reindexCount value(value will be incremented by 1 from the existing value)
• Asynchronous reindexing
• Property index updates is assigned to a dedicated background job and when the indexing is done, the property definition will be switched back to synchronous updates mode
• To enable this, 

Action	Name	Type	Value
Add	reindex-async	Boolean	true
Update (value from false->true)	reindex	Boolean	true

• Note: Both these updates to be persisted in one shot (do not trigger save with one property update)
• As a result of this, below property would be automatically created. 

Name	Type	Value
async	String	async-reindex

• Navigate to JMX console - http://localhost:4502/system/console/jmx and invoke the MBean - PropertyIndexAsyncReindex#startPropertyIndexAsyncReindex.
• Once when the job is completed, 
• automatically created property async will be removed
• reindex property would be set to false
• reindexCount would be incremented to 1 from its existing value.