Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #632

pg_hint_plan

Show all headers | View raw

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 u[mgogala@pg91 ~]$ rpm -qa|grep pg_hint

pg_hint_plan93-1.1.3-1.el6.x86_64
[mgogala@pg91 ~]$ 
sing Red Hat derivative, like me, it's a simple RPM package. Check the 
content of the package, like this:

[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, the ordinary 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. However, there are cases 
when the optimizer plan goes awry. Any help that can be provided 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 are hard to maintain 
between version 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.



-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
Je suis Charlie

Back to comp.databases.postgresql | Previous | Next — Next in thread | Find similar

Thread

pg_hint_plan Mladen Gogala <gogala.mladen@gmail.com> - 2015-02-16 14:07 +0000
  Re: pg_hint_plan Dimitri Fontaine <dimitri@2ndQuadrant.fr> - 2015-02-16 15:24 +0100
    Re: pg_hint_plan Mladen Gogala <gogala.mladen@gmail.com> - 2015-02-16 14:59 +0000
    Re: pg_hint_plan Robert Klemme <shortcutter@googlemail.com> - 2015-02-17 08:29 +0100

csiph-web