-Learn the json in-build functions and see if one of them can replace one you made ad-hoc
- Seriously, replace json with normal tables for the most common stuff. That alone will speed up things massively. Maybe keep the old json around in case, but remove when it become old(?)
- Use views. Views allow to abstract over your databaee and allow to change internals
- If a big things is searching and that searching is nkind of complex/flexible, add FTS with proper indexing to your json then use it as first filter layer:
SELECT .. FROM table WHERE id IN (SELECT .. FROM search_table WHERE FTS_query) AND ...other filters
This speeeeeedupppp beautifully! (I get sub-second queries!)
- If your query do heavy calculations and your query planner show it, consider move them into a trigger and write the solved result into a table, and query that table instead. I need to loans calculations that requiere sub-second answers and this is how I solve it.
And for your query planner investigation, this handy tool is great:
- Change the field type from JSON to JSONB (better storage and the rest) https://www.postgresql.org/docs/13/datatype-json.html
-Learn the json in-build functions and see if one of them can replace one you made ad-hoc
- Seriously, replace json with normal tables for the most common stuff. That alone will speed up things massively. Maybe keep the old json around in case, but remove when it become old(?)
- Use views. Views allow to abstract over your databaee and allow to change internals
- If a big things is searching and that searching is nkind of complex/flexible, add FTS with proper indexing to your json then use it as first filter layer:
This speeeeeedupppp beautifully! (I get sub-second queries!)- If your query do heavy calculations and your query planner show it, consider move them into a trigger and write the solved result into a table, and query that table instead. I need to loans calculations that requiere sub-second answers and this is how I solve it.
And for your query planner investigation, this handy tool is great:
https://tatiyants.com/pev/#/plans