0

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!

1

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.