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

pgsql: Dig down into sub-selects to look for column statistics.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Dig down into sub-selects to look for column statistics.
Date: 2011-09-04 19:42:05
Message-ID: E1R0IZx-0001Ql-Qv@gemulon.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-committers
Dig down into sub-selects to look for column statistics.

If a sub-select's output column is a simple Var, recursively look for
statistics applying to that Var, and use them if available.  The need for
this was foreseen ages ago, but we didn't have enough infrastructure to do
it with reasonable speed until just now.

We punt and stick with default estimates if the subquery uses set
operations, GROUP BY, or DISTINCT, since those operations would change the
underlying column statistics (particularly, the relative frequencies of
different values) beyond recognition.  This means that the types of
sub-selects for which this improvement applies are fairly limited, since
most subqueries satisfying those restrictions would have gotten flattened
into the parent query anyway.  But it does help for some cases, such as
subqueries with ORDER BY or LIMIT.

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/1cb108efb0e60d87e4adec38e7636b6e8efbeb57

Modified Files
--------------
src/backend/utils/adt/selfuncs.c |  141 +++++++++++++++++++++++++++++---------
1 files changed, 108 insertions(+), 33 deletions(-)

pgsql-committers by date

Next:From: Tom LaneDate: 2011-09-04 23:10:17
Subject: pgsql: Fix #include problems in 9.1 branch.
Previous:From: Tom LaneDate: 2011-09-04 07:29:00
Subject: Re: Re: [COMMITTERS] pgsql: Remove "fmgr.h" include in cube contrib --- caused crash on a Ge

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