Ability to add query hinting to make use of pg_hint_plan

[aijorgenson]

It would be nice if there was a way to prefix queries with hints for extensions. For example, an officially recognized extension ( https://www.postgresql.org/about/news/pg_hint_plan-v160-released-2712/ ) pg_hint_plan which allows you to modify which index is utilized for a query.

https://github.com/ossc-db/pg_hint_plan

Syntax looks like so:

/*+ Something(my_table index_name) Something(my_other_table other_index_name) */
select * from my_table mt join my_other_table mot on mot.my_table_id = mt.id

[tpetry]

Thats a great idea 👍 Also had it on my list for some day.

Should it be one long string or more PHP like as an array?

Model::query()
  ->hintplan('Something(my_table index_name) Something(my_other_table other_index_name)')
  ->....

Model::query()
  ->hintplan(['Something(my_table index_name)', 'Something(my_other_table other_index_name)'])
  ->....

Hmm, hintplan as a method looks/sounds strange.

[aijorgenson]

@tpetry I'm relatively new to the PostgresSQL world, are there other extensions that could make use of a comment before the query like that?

Maybe ->queryPrefix() and then you can toss in whatever string you would like?

Model::query()
    ->queryPrefix('/*+ Something(my_table index_name) Something(my_other_table other_index_name) */')
    ->....

[tpetry]

Theres nothing else.

[aijorgenson]

->hint or ->hintPlan both sound great then! I'd imagine it'd be pretty simple to allow either a string or an array to be concatenated passed in?

[tpetry]

Sure. I could allowe both options.

[dangnhdev]

@tpetry I think we should take a look at https://github.com/spatie/laravel-sql-commenter for referrence and possible conflicts. I'm using this package to debug and analyze query.

[tpetry]

I don‘t see a conflict there. But I‘ll test it.