Groups > comp.databases.postgresql > #633

Re: pg_hint_plan

Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!newsfeed.fsmpi.rwth-aachen.de!newsfeed.straub-nv.de!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail
From	Dimitri Fontaine <dimitri@2ndQuadrant.fr>
Newsgroups	comp.databases.postgresql
Subject	Re: pg_hint_plan
Date	Mon, 16 Feb 2015 15:24:24 +0100
Organization	A noiseless patient Spider
Lines	40
Message-ID	<m261b2aq93.fsf@2ndQuadrant.fr> (permalink)
References	<pan.2015.02.16.14.08.17@gmail.com>
Mime-Version	1.0
Content-Type	text/plain
Injection-Info	mx02.eternal-september.org; posting-host="82f27a6143f65dbdc4592d5228b9c119"; logging-data="6705"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX18cbvalU5bKxfw5DxuyZMvl"
User-Agent	Gnus/5.13001 (Ma Gnus v0.10) Emacs/24.3.93 (darwin)
Cancel-Lock	sha1:vdn5UU28LHM+16/G6tQu17tkwG8= sha1:EWHem7h7YmH5UHLBX7CMQRbO8Rk=
Xref	csiph.com comp.databases.postgresql:633

Show key headers only | View raw

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

Back to comp.databases.postgresql | Previous | Next — Previous in thread | 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