For those who don’t know, it is now possible to use hints on PgSQL. Here
is how things work:
- Download and install the extension from the home page:
If you are using Red Hat derivative, like me, it’s a simple RPM package. Check the content of the package, like this:
[mgogala@pg91 ~]$ rpm -qa|grep pg_hint
[mgogala@pg91 ~]$ rpm -ql pg_hint_plan93-1.1.3-1.el6.x86_64
Put the following parameters into your postgresql.conf:
shared_preload_libraries = '/usr/pgsql-9.3/lib/pg_hint_plan.so'
pg_hint_plan.enable_hint_tables = on
Now you need to restart your PostgreSQL and you’re done, the extension is ready to use. It is a good practice to run the following statement:
postgres=# create extension if not exists pg_hint_plan;
Now, users can do the following:
mgogala=# explain select * from emp where empno=7934;
Seq Scan on emp (cost=0.00..1.18 rows=1 width=43)
Filter: (empno = 7934)
Without any hints, the planner chooses sequential scan. Now, let’s try with a hint:
mgogala=# explain select /*+ IndexScan(emp emp_pkey) */ * from emp where
Index Scan using emp_pkey on emp (cost=0.14..8.15 rows=1 width=43)
Index Cond: (empno = 7934)
Voila, the optimizer hints are ready to use. This works on 9.3.6:
mgogala=# select version();
PostgreSQL 9.3.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.7 20120 313 (Red Hat 4.4.7-11), 64-bit (1 row)
There is also RPM package for 9.4. Please note that the planner has correctly told you that the cost of an index scan is higher than the cost of sequential scan. In this particular case, it is correct. The whole table fits into a single block and sequential scan of that block is the fastest method. Index scan needs to read the index root block, then the index leaf block and only then the table block. The optimizer has incorrectly estimated the cost of a sequential scan, because it cannot be “0.00”. That is an example of an optimizer bug and yet another reason to have hints at hand.
There are also cases when the optimizer plan itself goes awry. Any help that can be provided in such situations is beneficial. Hints give you better granularity then “set enable_indexscan” or similar commands which affect the entire session and can negatively impact the subsequent SQL commands. Hints are not ideal and do require some maintenance between versions, but are sometimes the only solution when performance of your SQL has to be improved quickly. You know your data better than the planner and hints give you the means of overriding an incorrect plan for a single SQL statement. Hints are simply a tool that can be used in fire fighting situations.
It is encouraging to see such extension in a community which was up in arms against the proponents of hints not so long ago. There is still the ridiculous “we don’t want hints” part on the PostgreSQL wiki, but there is also the extension which allows anyone who does want them to use them. PostgreSQL community has just discovered democracy.
Another interesting PostgreSQL package by the same author is the following:
That package offers some, but not all, functionality found in Oracle’s DBMS_STATS package, in particular locking and exporting the table stats. That functionality has not been offered by VACUUM utility which PostgreSQL uses to gather stats on the tables.