On Thu, Dec 8, 2016 at 3:53 AM, Srinivas Karthik V
<skarthikv.i...@gmail.com> wrote:
> Dear PostgreSQL Hackers,
> I am working in PostgreSQL 9.4.* optimizer module. In costsize.c file and
> final_cost_hashjoin() function, the innerbucketsize is either:
> a) calculated using a cached copy
>                                                       OR
> b)  calculated afresh using statistics captured by the following code
> snippet:
> thisbucketsize =   estimate_hash_bucketsize(root,
> get_leftop(restrictinfo->clause),virtualbuckets);
> For the query I used, if I disable the caching for calculating the
> innerbucketsize, I get a different plan with cost change of around 1000
> units.
> 1) Can you please let me know if innerbucketsize*innerpathrows captures the
> maximum bucket size?
> 2) why is it not calculated afresh all the time?

Well, #2 is answered there right in the comments:

                         * Since we tend to visit the same clauses
over and over when
                         * planning a large query, we cache the
bucketsize estimate in the
                         * RestrictInfo node to avoid repeated lookups
of statistics.

I assume the person who wrote the comment thought that the answer
wouldn't change from one call to the next, and therefore it was safe
to cache.  I don't know why that isn't the case for you.

As to question #1, there's a comment for that, too, a little further down:

                 * The number of tuple comparisons needed is the number of outer
                 * tuples times the typical number of tuples in a hash
bucket, which
                 * is the inner relation size times its bucketsize
fraction.  At each
                 * one, we need to evaluate the hashjoin quals.  But actually,

So innerbucketsize*innerpathrows represents the expected number of
comparisons that we expect to need to perform per hash probe.

Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to