Determine which indices are needed for efficient execution of a query about sqlc HOT 5 OPEN

Can I suggest a slightly different process?

How about generating a second function for each SELECT that does an EXPLAIN? So for example:

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

would generate a GetAuthor function but also an ExplainGetAuthor that would return explain results for that database. You could then add a test program that would run all those Explain statements and give results in your build process - whenever that was appropriate. I can imagine that folks won't always have DB access at compile time but a build pipeline would/could.

In addition this would allow people to track changes over time, decide which queries needed to be optimal, etc.

from sqlc.

There seems to exist something similar: https://github.com/ankane/dexter But it is not built-in into a compilation step.

from sqlc.

@lyda An interesting proposal. In a way it makes sense, it is like the benchmarks you would have in a test suite where tests would fail if expected performance would get too low.

It would mean that developers would have to manually add indices and then during testing you could check:

• Are there indices which are missing and do not exist.
• Are there indices where are there but are not ever used.

This could also be something similar to "coverage" - do all your tests at the end cover the indices you have and are there indices they could over but are missing. So you would run whole test suite and check that (instead of having an explicit tests to validate indices - I worry that then calls you might be doing would be repeated with calls you do during regular tests).

Which brings me then to the final point:

This might best live outside of a particular program/test suite/sqlc. You have test cases to test your code (which can include running against a realistic dump of data). Then you:

• Create database instance. Instrument it with extensions to store all queries made.
• You run your test suite.
• You check all queries - where there slow queries, where there queries which used scans, are there indices no query ever used.
• You repeat, this time creating all possible hypothetical queries.
• Now you check if any query would use any of the hypothetical queries.

Sqlc would then only have to support some hook to be able to give you information about the schema to create all the hypothetical queries. But one could probably find a way to parse schema from sql also without sqlc to figure that out.

from sqlc.

Exactly. sqlc will provide the Explain* functions but then it's up to you to run one or more of them in whatever environment you see fit. I think the real win here is if we can parse the response to EXPLAIN and provide meaningful feedback to the developer.

I might try and do it for cockroachdb if I get some time. Bank holiday coming up in a few weeks.

from sqlc.

Note: This could even be run in production systems to track how certain statements are being optimised over time.

from sqlc.