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 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: References: 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 Mladen Gogala 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