How to create an index for a complex query?

SQL++
1

I have a complex query containing a join, a union, a sub-query, some collection expressions and some predicates.

I am wondering what is the best way to go about creating an index for this query.

  1. Is it possible to create an index for such a complex query?
  2. Should I try to simplify the query into multiple queries and create the indexes on the individual queries?
  3. Should I create multiple indexes rather than 1 complex index?

The following is a sample of the query

select user, array_agg(group.id) as groups from write group
join write user
on keys group.userIds
where group.userIds is not missing
and group.docType = “group”
and user.docType = “user”
and user.extension.TestUser is not missing
and (LOWER(user.extension.TestUser.personalDetail.firstName) like ‘%andrew%’
or LOWER(user.extension.TestUser.personalDetail.middleName) like ‘%andrew%’
or LOWER(user.extension.TestUser.personalDetail.lastName) like ‘%andrew%’
or LOWER( user.extension.TestUser.personalDetail.nickName ) like ‘%andrew%’)
group by user
union
select as groups , user from write user
where user.docType = “user”
and user.extension.TestUser is not missing
and every item in (select u from write g unnest g.userIds u where g.userIds is not missing) satisfies user.id != item.u end
and (LOWER( user.extension.TestUser.personalDetail.firstName) like ‘%andrew%’
or LOWER(user.extension.TestUser.personalDetail.middleName) like ‘%andrew%’
or LOWER(user.extension.TestUser.personalDetail.lastName) like ‘%andrew%’
or LOWER(user.extension.TestUser.personalDetail.nickName ) like ‘%andrew%’)

If there are any questions about this topic, please let me know.
Any guidence or direction to relevant documentation would be greatly appreciated.

Regards,
Damian

2

Each query block prepared/executed, so you need to create multiple indexes for each query block as if you are executing each query separately.