Skip site navigation (1) Skip section navigation (2)

pgsql: Rethink heuristics for choosing index quals for parameterizedpa

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Rethink heuristics for choosing index quals for parameterizedpa
Date: 2012-09-16 21:59:14
Message-ID: E1TDMry-0000sF-GD@gemulon.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-committers
Rethink heuristics for choosing index quals for parameterized paths.

Some experimentation with examples similar to bug #7539 has convinced me
that indxpath.c's original implementation of parameterized-path generation
was several bricks shy of a load.  In general, if we are relying on a
particular outer rel or set of outer rels for a parameterized path, the
path should use every indexable join clause that's available from that rel
or rels.  Any join clauses that get left out of the indexqual will end up
getting applied as plain filter quals (qpquals), and that's generally a
significant loser compared to having the index AM enforce them.  (This is
particularly true with btree, which can skip the index scan entirely if
it can see that the given indexquals are mutually contradictory.)  The
original heuristics failed to ensure this, though, and were overly
complicated anyway.  Rewrite to make the code explicitly identify each
useful set of outer rels and then select all applicable join clauses for
each one.  The one plan that changes in the regression tests is in fact
for the better according to the planner's cost estimates.

(Note: this is not a correctness issue but just a matter of plan quality.
I don't yet know what is going on in bug #7539, but I don't expect this
change to fix that.)

Branch
------
REL9_2_STABLE

Details
-------
http://git.postgresql.org/pg/commitdiff/4e54ae66a5a4ff3b3cdd2a2642a0180d6f8226a0

Modified Files
--------------
src/backend/optimizer/path/indxpath.c |  355 +++++++++++++++++++--------------
src/test/regress/expected/join.out    |   16 +-
2 files changed, 215 insertions(+), 156 deletions(-)

pgsql-committers by date

Next:From: Peter EisentrautDate: 2012-09-17 02:28:13
Subject: pgsql: PL/Python: Improve Python 3 regression test setup
Previous:From: Simon RiggsDate: 2012-09-16 18:55:53
Subject: pgsql: Fix bufmgr so CHECKPOINT_END_OF_RECOVERY behaves as a shutdownc

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group