Re: [HACKERS] Effect of caching hash bucket size while costing

[Tom Lane]

Robert Haas  writes:
> On Thu, Dec 8, 2016 at 3:53 AM, Srinivas Karthik V
>  wrote:
>> 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.

That was me.  AFAICS, the only way this could change is if virtualbuckets
changes, which would require the results of ExecChooseHashTableSize to
change, which probably means inner_path_rows changed.  So I suspect this
got broken by the introduction of parameterized paths; but there's not
enough info here to confirm whether we're dealing with a parameterized
path or not.

If that is it, I wonder whether we could redefine the cached value so
that it doesn't depend on virtualbuckets.  If not, we could fall back
to only using the cache for nonparameterized inner paths.

regards, tom lane


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

Re: [HACKERS] Effect of caching hash bucket size while costing

[Robert Haas]

On Thu, Dec 8, 2016 at 3:53 AM, Srinivas Karthik V
 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:
http://www.postgresql.org/mailpref/pgsql-hackers