Generic SQL generation writes ANSI SQL and stops. Postgres has thirty years of features past that, and the query you want usually needs one of them.
A model writing lowest-common-denominator SQL produces a correlated subquery where a window function belongs, and a chain of joins where a lateral would be clearer and faster. Grounding in the real catalog — including the column types — is what makes the Postgres-shaped answer available.
Postgres will tell you exactly what it did — `EXPLAIN (ANALYZE, BUFFERS)` is one of the best diagnostic tools in any database — and the reason most slow queries stay slow is that the output is dense. SQLore reads the plan and reports the cause in a sentence: a sequential scan where an index exists but cannot be used, a nested loop over far more rows than the planner estimated, a sort spilling to disk because `work_mem` is too small for it.
Two queries that return identical results can differ by three orders of magnitude in cost, and which one is right depends on how big the tables actually are. Because the introspection step caches planner statistics, the generator has a sense of cardinality before it writes anything — which is the difference between joining then filtering and filtering then joining.
Everything above is analysis. SQLore validates every generated statement to a single read-only SELECT, so it can tell you which index to add and write the `CREATE INDEX` for you to read, but applying it is yours. No DDL, no writes, no `ALTER SYSTEM` — the tool has no path to any of them.
More answers on the main FAQ, or ask us directly.
PostgreSQL
MySQL, in depth
For people who already write SQL
Every page in this section covers a different question about the same product. See all solutions.
Start free, upgrade when you are asking enough questions for it to matter.
