This command line tool gets planner hints for PostgreSQL from SQL, instead of getting the query results. The generated planner hints can be used with pg_hint_plan extension module.
phint itself doesn't require pg_hint_plan for execution. However, to use planner hints PostgreSQL requires pg_hint_plan module. phint generates planner hints that can be used by pg_hint_plan. So please install pg_hint_plan when you use planner hints generated by phint.
To install phint, run:
$ go get github.com/MasahikoSawada/phint
Make sure your PATH
includes the $GOPATH/bin
directory so your commands can be easily used:
export PATH=$PATH:$GOPATH/bin
To use planner hints in PostgreSQL you can use pg_hint_plan extension module, which enables us to planner hints in PostgreSQL.
phint sends the specified SQL to PostgreSQL server and get the query plan in the form of json (using by EXPALIN (FORMAT JSON)
. So please start PostreSQL server before using phint.
Run phint
like psql
command:
$ phint --host localhost -p 5432 -d mydb -c "SELECT * FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id AND tbl1.id < 1000"
then you will get planner hints with SQL in stdout:
/*+
Leading((tbl2 tbl1))
HashJoin(tbl2 tbl1)
SeqScan(tbl2)
IndexScan(tbl1 tbl1_pkey)
*/
EXPLAIN SELECT * FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id AND tbl1.id < 1000
phints generates three kinds planner hints: Leading
, Joins
and Scans
. These behavior might change in the future.
You can also execute SQL command from file using -f
option:
$ vi /path/to/sample.sql
SELECT * FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id AND tbl1.id < 1000
$ phint --host localhost -p 5432 -d mydb -f /path/to/sample.sql
/*+
Leading((tbl2 tbl1))
HashJoin(tbl2 tbl1)
SeqScan(tbl2)
IndexScan(tbl1 tbl1_pkey)
*/
EXPLAIN SELECT * FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id AND tbl1.id < 1000
In addition, phint can accept query plans in forms of json directly from stdin using --input-plan
option:
$ psql -Atqc "EXPLAIN (FORMAT JSON) SELECT * FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id AND tbl1.id < 1000" | phint --input-plan
/*+
Leading((tbl2 tbl1))
HashJoin(tbl2 tbl1)
SeqScan(tbl2)
IndexScan(tbl1 tbl1_pkey)
*/
Note that you need to execute EXPLAIN
command with FORMAT JSON
option to get json formatted query plan, and need to use -Atq
psql options to get only query plan string.
The following plan nodes and features are not tested
- Subquery
- Parallel query
- UPDATE/DELETE/DELETE
If you faced any issues, please create an Issue on this repository with the following information.
- Query you executed
- Query plan you got
- Hint plans phint generated
- PostgreSQL version
- Error messages if exists
- PostgreSQL 9.6 or later