Planner can't seem to use partial function indexes with parameterfrom join - Mailing list pgsql-general

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