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

Groups > comp.databases.postgresql > #632 > unrolled thread

pg_hint_plan

Back to article view | Back to comp.databases.postgresql

Contents

  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

#632 — pg_hint_plan

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

[toc] | [next] | [standalone]

#633

Mladen Gogala <gogala.mladen@gmail.com> writes:
> Hints give you better granularity then "set enable_indexscan" or similar 
> commands which affect the entire session and can negatively impact the 

Never use enable_ options outside of interactive debugging to understand
query plans, really. It's meant to only that, debug activities.

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

Before using hints, consider improving the statistics granularity used
by PostgreSQL query planner with simple commands

  http://www.postgresql.org/docs/9.3/static/planner-stats.html
  
  http://www.postgresql.org/docs/9.3/static/sql-altertable.html
   ALTER TABLE name ALTER [ COLUMN ] column_name SET STATISTICS integer
  

When that's not enough, consider adding the right indexes for the query,
as explained by http://use-the-index-luke.com/; and if that also fails
for you then consider rewriting the query. The most powerful rewriting
tool in PostgreSQL is the CTE WITH:

  http://www.postgresql.org/docs/9.3/interactive/queries-with.html

Again, as soon as you use hints, then you need to review every once in a
while that they are still effective, because they won't be re-evaluated
against data growth as the usual PostgreSQL statistics system are. Also
each new version of PostgreSQL will come with a better planner and
optimiser, so you will need to review all hints and remove the rotted
ones. Yes, hints rot.

-- 
Dimitri Fontaine
PostgreSQL DBA, Architecte

[toc] | [prev] | [next] | [standalone]

#634

On Mon, 16 Feb 2015 15:24:24 +0100, Dimitri Fontaine wrote:

> Again, as soon as you use hints, then you need to review every once in a
> while that they are still effective, because they won't be re-evaluated
> against data growth as the usual PostgreSQL statistics system are. Also
> each new version of PostgreSQL will come with a better planner and
> optimiser, so you will need to review all hints and remove the rotted
> ones. Yes, hints rot.

Yes, they indeed do rot. As I have said in my post, hints are only a fire 
fighting solution of the last resort. And they are necessary as such. My 
deepest gratitude goes to the author(s) of the extension who have made 
them available.



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

[toc] | [prev] | [next] | [standalone]

#635

On 16.02.2015 15:24, Dimitri Fontaine wrote:

> Again, as soon as you use hints, then you need to review every once in a
> while that they are still effective, because they won't be re-evaluated
> against data growth as the usual PostgreSQL statistics system are.

Ideally your database monitoring would catch queries that deteriorate - 
be it because of hints or other reasons.

> Also
> each new version of PostgreSQL will come with a better planner and
> optimiser, so you will need to review all hints and remove the rotted
> ones. Yes, hints rot.

You will want to do testing with any new version of PostgreSQL before it 
goes into production anyway - regardless whether you use hints or not. 
While I agree to the general tendency to rather use automated mechanisms 
of finding a proper plan, this is not a differentiator or a reason 
against hints IMHO.

Kind regards

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web