Rerunning the text-to-SQL test suite from the SmartPostgres.com review

Dec 5, 2025, 9:35:27 AM

Table of contents

Introduction

AI development constantly moves forward with both small and large improvements, and SQLAI.ai is no exception. Our new app (app.sqlai.ai) is a complete rebuild of our features with many visible and under‑the‑hood upgrades. I was curious to see how it performs on the tests that Brent Ozar at SmartPostgres.com ran on the old app.

To start, I connect SQLAI to Stack Overflow’s test database by going to the database connector and entering the credentials:

postgres://readonly:511e0479-4d35-49ab-98b1-c3a9d69796f4@query.smartpostgres.com/stackoverflow

With the connection in place, the SQL generator can access the database schema when generating queries. You can also run the generated queries directly against the database if desired. Brent’s review includes two text‑to‑SQL prompts and one SQL optimization. I re‑ran all of them in the new app, running each prompt only once to keep the test as authentic as possible. The target database engine is PostgreSQL version 16.

Test 1: Simple text-to-SQL

Prompt:

Find the top-scoring posts from 2023

The old app responded with:

SELECT id, title, score
FROM posts
WHERE creationdate >= '2023-01-01' AND creationdate < '2024-01-01'
ORDER BY score DESC;

The new app responded with:

SELECT
  id,
  title,
  posttypeid,
  COALESCE(score, 0) AS score,
  creationdate,
  owneruserid,
  viewcount,
  tags
FROM posts
WHERE creationdate >= TIMESTAMP '2023-01-01 00:00:00'
  AND creationdate <  TIMESTAMP '2024-01-01 00:00:00'
ORDER BY COALESCE(score, 0) DESC, creationdate DESC
LIMIT 10;

Brent criticized the missing LIMIT in the old generation, which could overload the database on large tables. The new generation includes a LIMIT, returns additional useful columns, uses explicit TIMESTAMP bounds, and orders by score and creation date.

Test 2: Complex text-to-SQL

Prompt:

Find posts whose commentcount does not match the number of comments that actually exist for that post

The old app responded with:

SELECT p.id AS post_id, p.commentcount AS reported_comment_count, COUNT(c.id) AS actual_comment_count
FROM posts p
LEFT JOIN comments c ON p.id = c.postid
GROUP BY p.id
HAVING p.commentcount != COUNT(c.id);

The new app responded with:

SELECT
  p.id,
  p.title,
  p.commentcount AS recorded_commentcount,
  COALESCE(c.comment_count, 0) AS actual_commentcount
FROM posts p
LEFT JOIN (
  SELECT postid, COUNT(*) AS comment_count
  FROM comments
  GROUP BY postid
) c ON p.id = c.postid
WHERE COALESCE(p.commentcount, 0) <> COALESCE(c.comment_count, 0);

Brent noted the old query didn’t account for cases “where there are no rows in the comments table, but posts.commentcount is > 0.” The new query handles that with COALESCE. It also computes the comment counts in a subquery before joining to posts. This pattern often performs better on large datasets and avoids the row explosion that can occur when aggregating after a join.

Test 3: SQL optimization

The next test is to optimize this query using the SQL optimizer:

SELECT *
FROM posts
WHERE tags LIKE '%<postgresql>%'
ORDER BY score DESC
LIMIT 100;

The old app suggested two indexes to speed up execution:

CREATE INDEX idx_posts_tags ON posts USING gin (tags gin_trgm_ops);
CREATE INDEX idx_posts_tags_postgresql ON posts (score)
WHERE tags LIKE '%<postgresql>%';

The new app suggests the same indexes but without the confusing, incorrect remarks present in the old output (for example, claiming the LIKE pattern doesn’t start with a wildcard). The recommendations are on point: these indexes can significantly speed up the query without changing its results. Crucially, the optimizer doesn’t rewrite the query; it focuses on plan‑changing indexes instead—something many SQL optimizers don’t do.

First, it recommends enabling trigram support and adding a GIN index so LIKE '%...%' can be indexed:

-- 1) Enable trigram support (once per DB)
CREATE EXTENSION IF NOT EXISTS pg_trgm;


-- 2) Trigram GIN index so LIKE '%...%' can use an index
CREATE INDEX IF NOT EXISTS posts_tags_trgm_idx
  ON posts USING gin (tags gin_trgm_ops);

Optionally, it suggests a partial btree index to avoid sorting for this exact predicate:

-- 3) Optional: partial btree index to avoid sorting for this exact predicate
--    Note: only helps this specific tag pattern; great if queried frequently.
CREATE INDEX IF NOT EXISTS posts_score_postgresql_idx
  ON posts (score DESC)
  WHERE tags LIKE '%<postgresql>%';

Unlike the old generation, the new optimizer includes a clear explanation of what it recommends and why, plus additional considerations.

Conclusion

Across all three tests, the new SQLAI app shows measurable improvements:

  • Safer defaults (e.g., sensible LIMITs)
  • More robust logic (handling edge cases with COALESCE and better aggregation patterns)
  • Practical, accurate optimization advice that favors index changes over risky query rewrites

Overall, it’s a solid step forward from the version Brent originally reviewed.