Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: Mladen Gogala Newsgroups: comp.databases.postgresql Subject: pg_hint_plan Date: Mon, 16 Feb 2015 14:07:38 +0000 (UTC) Organization: A noiseless patient Spider Lines: 88 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Injection-Date: Mon, 16 Feb 2015 14:07:38 +0000 (UTC) Injection-Info: mx02.eternal-september.org; posting-host="9dbb49b777b4f27f09e09421fc52f8c8"; logging-data="28213"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/HUfW0uKJ1y9IepBTOzT70" User-Agent: Pan/0.139 (Sexual Chocolate; GIT bf56508 git://git.gnome.org/pan2) Cancel-Lock: sha1:kH/Lo4/nuDrEz1U3vKGAzRu63I0= Xref: csiph.com comp.databases.postgresql:632 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