Hello all,
I am having quite an interesting problem trying to get the planner to use my indexes as intended in my setup.
I am using partial functional indexes that have a different function parameter based on the record type.
A dynamically generated query using unions that are more explicit about index usage is significantly faster (10-50x) that my preferred plain sql approach using joins.
I have reduced my scenario to a minimal test case with inline comments to illustrate the issue here https://gist.github.com/a-mckinley/1b0e95142789cbc09121b71a83d03f45
Is there something that I am missing to allow the planner to use the underlying indexes? Or is the scenario too complex and should I stick with dynamic sql?
Best regards,
Alastair