PostgreSQL+SQLFlash

Take the optimization of a slow SQL in PostgreSQL as an example.

In the business system of an educational platform, we identified a SQL performance bottleneck. Let’s see how SQLFlash enhances the performance of SQL statements.

1. SQLFlash Optimized SQL Statement

What optimizations did SQLFlash perform?

1. Eliminate Sorting in Subqueries without LIMIT: Remove redundant ORDER BY sorting in the second subquery, which does not affect the final query results without LIMIT and adds performance overhead.
2. Implicit Conversion: Explicitly convert the string value in the date_of_birth filter condition to DATE type to avoid implicit conversion that may cause potential performance issues.
3. GROUP BY Clause Elimination: Remove unnecessary GROUP BY clause on primary key in subquery. Since student_id is the primary key of students table and no aggregate functions are used, the GROUP BY can be safely eliminated to improve performance.

Index Advise

1
2

CREATE INDEX idx_students_gender_dob ON students (gender, date_of_birth);
CREATE INDEX idx_enrollments_student_course ON enrollments (student_id, course_id);

1. The query applies filtering conditions on gender and date_of_birth in the students table. A composite index on these columns allows PostgreSQL to efficiently filter rows and reduce the number of scanned tuples, especially for selective queries combined with a LIMIT clause.
2. While the query does not explicitly include course_id, composite indexing on student_id and course_id is beneficial for scenarios where these columns are commonly used together in filtering or join operations. This helps optimize access patterns for frequent queries involving enrollments and minimizes the cost of table scans.

2. Performance Improvement Comparison

Query Execution Time:

• Before Optimization: 2165.777 ms
• After Optimization: 4.640 ms

Run origin SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

test=\# SELECT s.student_id,e2.student_id
FROM (select student_id from students where students.gender = 'Female'
                                        AND students.date_of_birth <= '2000-01-01' group by student_id) as s
left join   (SELECT e.student_id
                       FROM enrollments e
                       WHERE e.course_id <10000000
                       ORDER BY student_id DESC) as e2
  on s.student_id=e2.student_id
LIMIT 100;
Time: 2165.777 ms (00:02.166)

Add index And run Optimized SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

test=\# CREATE INDEX idx_students_gender_dob ON students (gender, date_of_birth);
CREATE INDEX idx_enrollments_student_course ON enrollments (student_id, course_id);
CREATE INDEX
Time: 6673.243 ms (00:06.673)
CREATE INDEX
Time: 5355.937 ms (00:05.356)
test=\# SELECT s.student_id,e2.student_id
FROM (select student_id from students where students.gender = 'Female'
                                        AND students.date_of_birth <= DATE '2000-01-01') as s
left join   (SELECT e.student_id
                       FROM enrollments e
                       WHERE e.course_id <10000000
                       ) as e2
  on s.student_id=e2.student_id
LIMIT 100;
Time: 4.640 ms