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

Groups > comp.databases.postgresql > #633

Re: pg_hint_plan

Show all headers | 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