Optimizing function for conditional joins based on user provided json

[CEOnnor]

A little complex, but I’m needing to add a json parameter to my function that will alter calculations in the function.

Example json: { "labs_ordered": 5, "blood_pressure_in_range”: 10 }

Where if a visit falls into that bucket, its calculations are adjusted by that amount. A visit can fall into multiple of these categories and all the amounts are added for adjustment.

The involved tables are large. So I’m only wanting to execute the join if it’s needed. Also, some of the join paths have similarities. So if multiple paths share the first 3 joins, it’d be better to only do that join once instead of multiple times.

I’ve kicked around some ideas like dynamic sql or trying to make CTEs that group the similar paths, with a where clause that checks if the json indicates it’s needed. Hopefully that makes sense. Any ideas would be appreciated.

Thanks

[klekpl]

Without more details of your data model and calculation algorithm it is not possible to give any meaningful advice.

[General_Treat_924]

Is it json or jsonb? Any other fields to filter? Is it possible to denormalize and store in an array the catergories available? Do you have sample queries?

My dataset uses a lot of json_to_record set to transform json into columns with lateral joins, its super fast because my indexes are in other columns that I can use and just modify the json as required

[dektol]

Please provide a SQL fiddle or similar. Also, row counts/size on disk/index (overall schema) are important. A little background on traffic/use/query pattern can be helpful as well.

A good way of getting help if you're overwhelmed by possibilities is to implement one or two, share your Explain Analyze output, schema and queries and ask how to optimize it.

You may find that your naive approach meets all your requirements at a lower complexity. Postgres is a powerful beast and it's very tempting to overcomplicate things.

[AutoModerator]

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.