I Used AI (Kiro) to Improve an Aurora DSQL N+1 Problem from 90 Seconds to 8 Seconds

Last year, I had the opportunity to develop an API to support a blog using Amazon Aurora DSQL, which became GA (generally available) in May 2025. During that work, I ran into an issue where the response time of tag searches degraded in proportion to the number of articles.

In this article, I share the process of investigating the cause using an AI assistant tool (Kiro) and reducing a process that took about 90 seconds down to 8 seconds (a 91% improvement), the log evidence supporting it, and how I dealt with a side effect that occurred during the fix.

Test Environment

The development environment was as follows. The number of records was prepared to match a production-equivalent data volume.

• Database: Amazon Aurora DSQL
• Compute: AWS Lambda (Python 3.12)
• Data scale:
  • Article data: approx. 60,000 records
  • Tag data: approx. 7,300 records
  • Mapping table : approx. 431,500 records

1. Confirming the Issue (90-second latency)

In the blog API under development, searching for articles with a specific tag (e.g., AWS) resulted in timeouts or extreme delays. First, I measured actual values using the curl command.

Measuring response time

# Command to run (measured with time)
time curl -s "API_URL?tags={tag_name}&limit=3" > /dev/null

The response time appeared to degrade linearly in proportion to the number of articles (number of matching hits).

2. AI Hypothesis and Verification (EXPLAIN ANALYZE)

Suspecting an inefficient loop process on the application side (the N+1 problem) given the abnormal latency of 90 seconds, I asked the AI assistant (Kiro) to investigate.

Kiro's autonomous investigation preparation

When I explained the situation, Kiro immediately estimated the cause and, going beyond merely proposing a solution, autonomously completed everything from "generating a verification script" to "preparing for execution", and asked for permission to run it.

Kiro:
"The N+1 problem is highly likely. You should use EXPLAIN ANALYZE in Aurora DSQL (PostgreSQL-compatible) to obtain the actual query execution plan.
I have created an EXPLAIN ANALYZE script. Shall I run it?"

After checking the SQL in the presented Python script and confirming that there was no risk of data destruction (it was read-only), I gave permission to run it. In practice, all the human side did was "code review and approval."

Data scale of the investigation target

For the verification, I checked the statistics of the tag mapping table, which was the performance bottleneck.

For about 60,000 articles, an average of more than 7 tags are assigned, resulting in a total of 430,000 rows. It was suspected that inefficient access was being performed on a table of this scale.

Execution plan analysis results

I ran the script generated by Kiro and obtained the following EXPLAIN ANALYZE log.

Execution Time: 31913.008 ms

->  Nested Loop  (actual time=2583.240..13677.777 rows=28074 loops=1)
    ->  Index Only Scan on posts_pkey (actual rows=1 loops=28079)
        -- ↑ 28,079 loops (Index Scan) occurred
    ->  Index Only Scan on post_summary_pkey (actual rows=1 loops=28074)
        -- ↑ 28,074 loops (Index Scan) occurred

From the log, it was identified that a total of more than 56,000 Index Scans occurred.
It was concluded that the cause of the 90-second timeout was that, for the roughly 28,000 article IDs tagged "aws" extracted from the 430,000-row post_tag table, a re-search was being run one by one in a loop.

3. The Fix (From subquery to JOIN)

The AI suggested that "changing the IN (SELECT ...) subquery structure to a JOIN allows the PostgreSQL optimizer to choose an efficient join process."

Code before the fix (Python)

# Using a subquery (anti-pattern)
where_conditions.append("""
    bm.id IN (
        SELECT blog_post_id
        FROM post_tag
        WHERE tag_name = ANY(%s)
        GROUP BY blog_post_id
        HAVING COUNT(DISTINCT tag_name) = %s
    )
""")

Code after the fix

# Using JOIN
from_clause = """
    FROM blogpost_meta bm
    JOIN post_tag bat ON bm.id = bat.blog_post_id -- Changed to INNER JOIN
    LEFT JOIN blogpost_ribbon br ON bm.id = br.blog_post_id
    LEFT JOIN blogpost_ai_summary bas ON bm.id = bas.id
"""

query = f"""
    SELECT ...
    {from_clause}
    WHERE bat.tag_name = ANY(%s) -- Filter in the WHERE clause
    GROUP BY bm.id, ...
    HAVING COUNT(DISTINCT bat.tag_name) = %s
    ORDER BY bm.first_published_at DESC
"""

I updated the Lambda function and measured again.

4. Confirming the Improvement

After the fix, I measured with the curl command under the same conditions.

For the "aws" tag, which has a large amount of data, I confirmed a reduction of about 91%, from 90 seconds to 8 seconds. From this, I concluded that the N+1 problem had been resolved.

5. Side Effect and Its Handling (Sort order)

Performance improved, but I found a new problem: "the sort order of the articles (by publication date) was broken."

Issue log

curl -s "API_URL?author=suzuki-ryo&limit=10" | jq '.articles[] | .published_at'

"2025-11-25T05:25:49.676000"
"2025-01-10T06:27:29.620000"
"2025-11-25T14:27:51.467000"

The dates are not in descending order.
The investigation revealed that when the DISTINCT ON clause in the query was removed as part of the N+1 countermeasure, it became inconsistent with the ORDER BY clause. Under PostgreSQL's specification, when DISTINCT ON is used, the first argument of ORDER BY must match it, but after removal that constraint no longer applied, resulting in unintended sort results.

The fix

I changed the ORDER BY clause to prioritize the publication date (bm.first_published_at), which is the actual requirement, rather than the primary key (bm.id).

-- Before the fix
ORDER BY bm.id, bm.first_published_at DESC 

-- After the fix
ORDER BY bm.first_published_at DESC

After redeploying, I confirmed that data could be retrieved in chronological order as intended.

"2026-01-25T13:21:17.055000"
"2026-01-17T16:34:16.857000"
"2026-01-16T05:50:00.259000"

6. Further Optimization (Multiple tag search)

Single-tag search became faster, but when multiple tags were specified (e.g., tags=aws,ec2,rds), a delay of about 19 seconds occurred again. The cause was that GROUP BY was being performed on a huge result set after joining all the tables.

To address this, I adopted the approach of "first narrowing down the IDs (filtering) in a subquery, and then performing the JOIN on that result."

-- Optimized version (conceptual)
SELECT bm.id, ...
FROM blogpost_meta bm
INNER JOIN (
    -- Narrow down the target IDs first here
    SELECT blog_post_id
    FROM post_tag
    WHERE tag_name = ANY(ARRAY['aws', 'ec2', 'rds'])
    GROUP BY blog_post_id
    HAVING COUNT(DISTINCT tag_name) = 3
) AS matched_posts ON bm.id = matched_posts.blog_post_id
...

With this change, I confirmed that the response time stabilizes at around 8 seconds even as the number of tags increases.

Summary

I introduced a case study of performance improvement using Kiro in an Amazon Aurora DSQL environment.

Although Aurora DSQL is a distributed database, it provides a PostgreSQL-compatible endpoint and can be started very simply. However, in situations like this where complex queries are written in raw SQL, the engineer's own implementation skill directly affects performance.

In performance tuning, "adding a missing index" is a standard measure that comparatively many engineers think of first. However, the bottleneck this time lay in the inefficiency of the join algorithm.

Reading from the EXPLAIN ANALYZE results "why the optimizer chose that execution plan" and deciding to refactor the query structure itself in order to steer it toward a more efficient join method (such as Hash Join) requires advanced database engineering knowledge.

I came to feel that Kiro is not just a code completion tool, but a reassuring partner that autonomously covers even the DBA domain of "execution plan analysis and structural reform with join algorithms in mind."
When adopting new database technologies like Aurora DSQL, please do try Kiro, an AI agent whose capabilities can be expected to shine.