PostgreSQL Hints and DBMS_STATS

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:

‭  ‬http://sourceforge.jp/projects/pghintplan/

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

pg_hint_plan93-1.1.3-1.el6.x86‭_‬64‭
[‬mgogala@pg91‭ ~]‬$
‭[‬mgogala@pg91‭ ~]‬$‭ ‬rpm‭ ‬-ql pg_hint_plan93-1.1.3-1.el6.x86‭_‬64
/usr/pgsql-9.3/lib/pg_hint_plan.so
/usr/pgsql-9.3/share/extension/pg_hint_plan–1.0–1.1.1.sql
/usr/pgsql-9.3/share/extension/pg_hint_plan–1.1.1–1.1.2.sql
/usr/pgsql-9.3/share/extension/pg_hint_plan–1.1.2–1.1.3.sql
/usr/pgsql-9.3/share/extension/pg_hint_plan–1.1.3.sql
/usr/pgsql-9.3/share/extension/pg_hint_plan.control

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‭;
CREATE EXTENSION

Now,‭ ‬users can do the following:

mgogala‭=‬#‭ ‬explain select‭ * ‬from emp where empno‭=‬7934‭;
QUERY PLAN
----------------------------------------------------
‭ ‬Seq Scan on emp‭  (‬cost‭=‬0.00..1.18‭ ‬rows‭=‬1‭ ‬width‭=‬43‭)
Filter:‭ (‬empno‭ = ‬7934‭)
(2‭ ‬rows‭)

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
empno‭=‬7934‭;
QUERY PLAN
---------------------------------------------------------------------
‭ ‬Index Scan using emp_pkey on emp‭  (‬cost‭=‬0.14..8.15‭ ‬rows‭=‬1‭ ‬width‭=‬43‭)
Index Cond:‭ (‬empno‭ = ‬7934‭)
(2‭ ‬rows‭)

Voila,‭ ‬the optimizer hints are ready to use.‭ ‬This works on‭ ‬9.3.6:

mgogala‭=‬#‭ ‬select version‭();

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:

http://pgdbmsstats.sourceforge.jp/pg_dbms_stats-en.html

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.‭

Advertisements

About mgogala

I am a consultant with an extensive Oracle DBA experience. I have worked on very large databases. I have worked with OPS and RAC since its inception.I am also a published book writer, having published two books about PHP. This blog is about the challenges and adventures in my professional life. Sorry, no family pictures here.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

3 Responses to PostgreSQL Hints and DBMS_STATS

  1. Oleg Dulin says:

    How would I do this with Amazon RDS ? Do you know if it works there ?

    • mgogala says:

      Hi Oleg,
      I believe that Amazon RDS is using PostgreSQL 9.2. You can simply install RPM. If all else fails, you can build the extension from the source.

      • Oleg Dulin says:

        They are on 9.3 and you don’t have access to the host, they install updates automatically. I just thought maybe you know of a way to do that. Anyways, I will google around, will keep you posted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s