Thread: Effect of caching hash bucket size while costing

Effect of caching hash bucket size while costing

From

Srinivas Karthik V

Date:

08 December 2016, 08:53:51

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

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?

For reference, below is the query I am using:

explain select i_item_id, avg(cs_quantity) , avg(cs_list_price) , avg(cs_coupon_amt) , avg(cs_sales_price) from catalog_sales, customer_demographics, date_dim, item, promotion where cs_sold_date_sk = d_date_sk and cs_item_sk = i_item_sk and cs_bill_cdemo_sk = cd_demo_sk and cs_promo_sk = p_promo_sk and cd_gender = 'F' and cd_marital_status = 'U' and cd_education_status = 'Unknown' and (p_channel_email = 'N' or p_channel_event = 'N') and d_year = 2002 and i_current_price <= 100 group by i_item_id order by i_item_id

and the hashclause which was tried was (item.i_item_sk = catalog_sales.cs_item_sk).

Thanks,

Srinivas Karthik

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

From

Robert Haas

Date:

10 December 2016, 21:38:27

On Thu, Dec 8, 2016 at 3:53 AM, Srinivas Karthik V
<skarthikv.iitb@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
typicalnumber 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

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

From

Tom Lane

Date:

11 December 2016, 03:46:35

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Dec 8, 2016 at 3:53 AM, Srinivas Karthik V
> <skarthikv.iitb@gmail.com> 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