laravel-postgresql-enhanced
laravel-postgresql-enhanced copied to clipboard
tpetry
Ability to add query hinting to make use of pg_hint_plan
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
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.
@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) */')
->....
->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 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.
