Optimizing Performance of Highly Nested Postgres SQL Query

Question

I have been working with a PostgreSQL database and I've encountered a SQL query that seems to have a high degree of nesting, which I suspect might be impacting its performance. I'm seeking advice on how to optimize this query for better efficiency.

As an example dataset, consider the following tables and data:

1. params with columns:

   • group_id (integer)
   • params_type (ENUM ['SCHOOL_DATA', 'PEOPLE_DATA', 'GEO_DATA', 'REPORT_DATA'])
   • calculated_values (jsonb)

2. params_groups with columns:

   • id (integer)
   • school_id (integer)
   • created_at (timestamp)

Example rows for params table:

group_id	params_type	calculated_values
1	SCHOOL_DATA	{"name": "School A", "status": "Private"}
1	PEOPLE_DATA	{"staffCount": 9, "studentsCount": 80}
1	GEO_DATA	{"country": "USA", "state": "XX", "street": "..."}
1	REPORT_DATA	{"gradesAvgs": {"math": 76, "physics": 80, "history": 78} "USA", "state": "XX", "street": "..."}
2	SCHOOL_DATA	{"name": "School B", "status": "Private"}
2	PEOPLE_DATA	{"staffCount": 15, "studentsCount": 130}
2	GEO_DATA	{"country": "Brazil", "state": "XX", "street": "..."}
2	REPORT_DATA	{"gradesAvgs": {"math": 88, "physics": 83, "history": 83} "Brazil", "state": "XX", "street": "..."}
3	SCHOOL_DATA	{"name": "School C", "status": "Public"}
3	PEOPLE_DATA	{"staffCount": 40, "studentsCount": 245}
3	GEO_DATA	{"country": "France", "state": "XX", "street": "..."}
3	REPORT_DATA	{"gradesAvgs": {"math": 92, "physics": 86, "history": 90} "France", "state": "XX", "street": "..."}
4	SCHOOL_DATA	{"name": "School D", "status": "Private"}
4	PEOPLE_DATA	{"staffCount": 13, "studentsCount": 100}
4	GEO_DATA	{"country": "India", "state": "XX", "street": "..."}
4	REPORT_DATA	{"gradesAvgs": {"math": 81, "physics": 74, "history": 71} "India", "state": "XX", "street": "..."}
5	SCHOOL_DATA	{"name": "School E", "status": "Public"}
5	PEOPLE_DATA	{"staffCount": 25, "studentsCount": 180}
5	GEO_DATA	{"country": "USA", "state": "XX", "street": "..."}
5	REPORT_DATA	{"gradesAvgs": {"math": 90, "physics": 84, "history": 78} "USA", "state": "XX", "street": "..."}

Example rows for params_groups table:

id	school_id	created_at
1	101	2023-08-15 09:00:00
2	102	2023-08-14 10:30:00
3	101	2023-08-14 15:45:00
4	103	2023-08-13 11:20:00
5	102	2023-08-13 14:10:00

Here's the query in question:

SELECT
    params.group_id AS params_group_id
FROM
    params
WHERE
    params.group_id IN (
        SELECT
            params.group_id
        FROM
            params
        WHERE
            params.group_id IN (
                SELECT
                    params.group_id
                FROM
                    params
                WHERE
                    params.group_id IN (
                        SELECT
                            params.group_id
                        FROM
                            params
                        WHERE
                            params.group_id IN (
                                -- Make sure we get the latest group of params per school in case a school has multiple params groups created in different time
                                SELECT
                                    DISTINCT ON (params_groups.school_id) params_groups.id
                                FROM
                                    params_groups
                                ORDER BY
                                    params_groups.school_id,
                                    params_groups.created_at DESC
                            )
                            AND params.params_type = 'REPORT_DATA'
                            AND (
                                params.calculated_values @> '{"math": 90}'
                            )
                    )
                    AND params.params_type = 'PEOPLE_DATA'
                    AND CAST(
                        (
                            params.calculated_values ->> 'headcount'
                        ) AS INTEGER
                    ) <= 25
            )
            AND params.params_type = 'SCHOOL_DATA'
            AND (
                params.calculated_values @> '{"status": "Private"}'
            )
    )
    AND params.params_type = 'GEO_DATA'
    AND (
        params.calculated_values @> '{"country": "USA"}'
    )
LIMIT
    10000

Obviously I cannot use all of the different filters in a single WHERE with AND operator between them because a row can never be of params_type = X and params_type = Y simultaneously

I suspect that the repeated subqueries and nested conditions might be causing a performance bottleneck. I'd appreciate any suggestions on how to optimize this query to make it more efficient. Perhaps there's a way to achieve the same result with fewer nested levels or using different SQL techniques?

Thank you for your insights and expertise!