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

pgsql: Fix some problems with selectivity estimation for partial

From: tgl(at)postgresql(dot)org (Tom Lane)
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Fix some problems with selectivity estimation for partial
Date: 2007-03-21 22:18:12
Message-ID: 20070321221813.015259FBAFE@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-committers
Log Message:
-----------
Fix some problems with selectivity estimation for partial indexes.

First, genericcostestimate() was being way too liberal about including
partial-index conditions in its selectivity estimate, resulting in
substantial underestimates for situations such as an indexqual "x = 42"
used with an index on x "WHERE x >= 40 AND x < 50".  While the code is
intentionally set up to favor selecting partial indexes when available,
this was too much...

Second, choose_bitmap_and() was likewise easily fooled by cases of this
type, since it would similarly think that the partial index had selectivity
independent of the indexqual.

Fixed by using predicate_implied_by() rather than simple equality checks
to determine redundancy.  This is a good deal more expensive but I don't
see much alternative.  At least the extra cost is only paid when there's
actually a partial index under consideration.

Per report from Jeff Davis.  I'm not going to risk back-patching this,
though.

Modified Files:
--------------
    pgsql/src/backend/optimizer/path:
        indxpath.c (r1.217 -> r1.218)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/indxpath.c.diff?r1=1.217&r2=1.218)
    pgsql/src/backend/utils/adt:
        selfuncs.c (r1.229 -> r1.230)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/selfuncs.c.diff?r1=1.229&r2=1.230)
    pgsql/src/test/regress/expected:
        select.out (r1.16 -> r1.17)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/select.out.diff?r1=1.16&r2=1.17)
    pgsql/src/test/regress/sql:
        select.sql (r1.12 -> r1.13)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/sql/select.sql.diff?r1=1.12&r2=1.13)

pgsql-committers by date

Next:From: User MarkirDate: 2007-03-22 00:10:14
Subject: bizgres - bizgres: Get resource queuing working.
Previous:From: User XzillaDate: 2007-03-21 21:54:24
Subject: nagiosplugins - nagiosplugins: Make README a bit more legible

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