[HACKERS] Effect of caching hash bucket size while costing

[Srinivas Karthik V]

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?

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

[HACKERS] Requesting some information about the small portion of source code of postgreSQL

[Srinivas Karthik V]

Dear PostgresSQL Hackers,
   I am working in optimizer module of postgreSQL 9.4.1. I am trying to
return a subplan for a query instead of full plan. For this I need to
return an intermediate plan (or path) from the DP lattice (i.e.  from
*RelOptInfo
*standard_join_search() *at* allpaths.c) *instead of the full optimal plan
(which is from the last level of *root->join_rel_level()).*
while doing so I am getting *error :variable not found in subplan target
lists* at function *fix_join_expr_mutator* at *setrefs.c.*

It will be great if you can give me some idea about why this error is
happening, since this error is happening at *fix_join_expr_mutator* function
at *setrefs.c.* Please give me more information about this portion of code.
Also I would like to know for what targetlist stands for.


Thanks,
Srinivas Karthik

Re: [HACKERS] Changing the result set to contain the cost of the optimizer's chosen plan

[Srinivas Karthik V]

Hi Tom,

Yes, we indeed get the cost of the plan at the first line itself. Somehow,
I missed this point. We just in fact implemented this functionality and its
working. Thanks again.

Regards,
Srinivas Karthik

On Tue, Jul 12, 2016 at 6:43 AM, Craig Ringer <cr...@2ndquadrant.com> wrote:

> On 11 July 2016 at 23:29, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
>> Srinivas Karthik V <skarthikv.i...@gmail.com> writes:
>> > Specifically, I have a Java program which calls
>> > ResultSet rs = statement.executeQuery("explain select * from table");
>> > I would like to change PostgreSQL such that ResultSet rs should contain
>> a
>> > field that contains also the cost of the optimizer chosen plan.
>>
>> Why do you need to change anything?  The cost is right there in the
>> first line of the result text.  It might be easier to parse out if
>> you use one of EXPLAIN's intended-to-be-machine-readable output
>> formats, though. <http://www.postgresql.org/mailpref/pgsql-hackers>
>
>
> Yeah - if we were going to do this at all, it'd want to be output that
> decomposes _all_ the explain output into columns.  But since we can emit
> json, xml, etc, I don't really see the point.
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

[HACKERS] Changing the result set to contain the cost of the optimizer's chosen plan

[Srinivas Karthik V]

Hello,

I am Srinivas and have been working inside PostgreSQL (mostly in the
optimizer module and few times in the executor module). I would like to
change PostgreSQL code such that it also returns the cost of the optimizer
chosen plan to the Java program (through JDBC) as part of the result set.

Specifically, I have a Java program which calls

ResultSet rs = statement.executeQuery("explain select * from table");

I would like to change PostgreSQL such that ResultSet rs should contain a
field that contains also the cost of the optimizer chosen plan.

If the above is not possible, also please let me know if there is some
other way I could get the cost of the plan in a single call to engine
through Java program.

Regards,
Srinivas Karthik

[HACKERS] PostgreSQL 8.3 index page count clarification

[Srinivas Karthik V]

Hi,
   For the user created indexes in PostgreSQL 8.3.6, I would like to
know which file or function populates the index data structure.
Specifically, where in the code is index-tuples and index-pages values
are fetched and updated.

Regards and Thanks in Advance,
Srinivas Karthik

[HACKERS] PostgreSQL 8.3 index page count clarification

[Srinivas Karthik V]

Hi,
  For the user created indexes in PostgreSQL 8.3.6, I would like to
know in which table (eg: pg_tablename) the index-tuple-count and
index-page-count meta-data statistics are stored.


Regards and Thanks,
Srinivas Karthik