Feature request: optimizer hints

[federico-razzoli]

What's up?

As far as I understand, optimizer hints are not supported.

I'm particularly interested to MariaDB / MySQL syntax:

< FROM | JOIN > <table_name> [ AS <alias >] < FORCE | USE | IGNORE > INDEX (< index_list >)

But any type of hint should be supported somehow.

[max-sixty]

Yes, interesting idea.

My sense is that these are mainly in transactional DBs — i.e. are less prevalent in DuckDB / BigQuery / Snowflake — and fairly DB-specific.

If that's correct, I'd think they'd be less of a focus for PRQL, though they could always be supplied within an S-String...

[federico-razzoli]

@max-sixty Yes, the syntax is purely for row-based databases and 100% specific to each database. And I understand your will to keep things standard. However, when indexes aren't used, a query might be completely unusable.

Based on the documentation, I don't understand how to insert s-strings in that position (after a table name). Would you please provide an example, and perhaps document it? Thank you!

[max-sixty]

It would be something like:

from s"SELECT first_name, id, age FROM employees FORCE foo bar"
...

...where FORCE foo bar is the optimizer hint — does that make sense? (Obv prql isn't that helpful in that specific query, it's intended to show the escape hatch...)