Thread: Source Code Help Needed
Hello,
I've been using Postgresql-8.0.1 (Release date: 2005-01-31) for my
research work and I guess I finally need some help with it...
I'm not trying to modify the existing functionality, but I want to add
few things. In particular, I'm calculating two new Cost values and I
need to use them while the query is executing. Please See code
snippets below-
1.) New Variables ADDED to src/include/nodes/plannodes.h
typedef struct Plan
{ Cost hutz_tbl_benefit; /* Benefit for TableAccess */ Cost hutz_idx_benefit;
/* Benefit for IndexScan */
}
2.) New Variables ADDED to src/include/nodes/relation.h
typedef struct Plan
{ Cost hutz_tbl_benefit; /* Benefit for TableAccess */ Cost hutz_idx_benefit;
/* Benefit for IndexScan */
}
3.) ADDITIONS to costsize.c
void cost_seqscan(Path *path, Query *root, RelOptInfo *baserel)
{ path->hutz_tbl_benefit = xxxxx; path->hutz_idx_benefit = xxxxx;
}
void cost_index(Path *path, Query *root, RelOptInfo *baserel, IndexOptInfo *index, List *indexQuals,
boolis_injoin)
{ path->hutz_tbl_benefit = xxxxx; path->hutz_idx_benefit = xxxxx;
}
However, after these modifications the server process crashes on
running a Join query like
"select s_suppkey,c_custkey from supplier,customer where s_suppkey>125
and s_suppkey<128 and c_custkey>100 and c_custkey<103 and
c_custkey=s_suppkey"
But, this query runs fine "select s_suppkey from supplier where
s_suppkey>125 and s_suppkey<128"
I'm tracing the point at which the process crashes and at this point
it seems to inside
src/backend/optimizer/path/joinrels.c>>make_rels_by_joins()
So, my question is, after adding the two new variables what other
modifications do I need to make for code to work, And later on, what
changes are reqd so that I can access these variables while executing
the Query Plan in lets say ExecutePlan() and its sub-functions like
ExecProcNode()...
Thanks to everybody on this group,
-Vikram Kalsi
MSEE PennStateUniv
Vikram Kalsi <vikramkalsi@gmail.com> writes:
> 1.) New Variables ADDED to src/include/nodes/plannodes.h
> 2.) New Variables ADDED to src/include/nodes/relation.h
> ...
> However, after these modifications the server process crashes on
> running a Join query like
> "select s_suppkey,c_custkey from supplier,customer where s_suppkey>125
> and s_suppkey<128 and c_custkey>100 and c_custkey<103 and
> c_custkey=s_suppkey"
Did you do a full recompile (make clean and rebuild) after modifying
these widely-known structures?
Unless you configured with --enable-depend, you can't expect that plain
"make" will recompile everything that needs recompiled.
regards, tom lane
Thanks Tom, that solved it...I added the new variables one at a time and did do a "make clean" on the first occasion but I must have forgotten to do it the second time... I have another question- The new variables that I've added to Plan and Path i.e. hutz_tbl_benefit and hutz_idx_benefit are being assigned values inside cost_seqscan() and cost_index() in costsize.c and this is done alongside startup_cost and total_cost. But, when I read the value of hutz_tbl_benefit and hutz_idx_benefit inside pg_plan_query() or later at the execution stage inside ExecProcNode(), the value is absent, but startup_cost and total_cost retain their values. So, I suppose that during the query planning and optimization stage, the value of the original variables in the plan are somehow copied to the plan which is finally returned inside pg_plan_query(). Could somebody direct me to the appropriate code/function(s) which does this copying so that I can add hutz_tbl_benefit and hutz_idx_benefit to that as well. Thanks in anticipation, Regards, On 5/25/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Vikram Kalsi <vikramkalsi@gmail.com> writes: > > 1.) New Variables ADDED to src/include/nodes/plannodes.h > > 2.) New Variables ADDED to src/include/nodes/relation.h > > ... > > However, after these modifications the server process crashes on > > running a Join query like > > "select s_suppkey,c_custkey from supplier,customer where s_suppkey>125 > > and s_suppkey<128 and c_custkey>100 and c_custkey<103 and > > c_custkey=s_suppkey" > > Did you do a full recompile (make clean and rebuild) after modifying > these widely-known structures? > > Unless you configured with --enable-depend, you can't expect that plain > "make" will recompile everything that needs recompiled. > > regards, tom lane >
Vikram Kalsi <vikramkalsi@gmail.com> writes:
> So, I suppose that during the query planning and optimization stage,
> the value of the original variables in the plan are somehow copied to
> the plan which is finally returned inside pg_plan_query().
Look in createplan.c --- there are a couple places in there you need to
fix.
regards, tom lane
I am a MSEE student at Penn State (University Park), for the past few months I have been working on modifying parts of PostgreSQL for my research work. I doubt if my current work would serve any purpose for pgsql since it is experimental and research oriented, but all the same, I have gained familiarity with parts of pgsql. I'm interested in Google's Summer of Code, but I would definitely need help, starting with selection of an idea to work on. So, if anybody from PostgreSQL would like to support this, then please get in touch with me as early as possible. By the way, I didn't see PostgreSQL in the list of Participating Organizations on http://code.google.com/summerofcode.html? Thanks and Regards, -Vikram Kalsi MSEE PennState vzk101 at psu dot edu www.personal.psu.edu/vzk101 On 5/25/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Vikram Kalsi <vikramkalsi@gmail.com> writes: > > So, I suppose that during the query planning and optimization stage, > > the value of the original variables in the plan are somehow copied to > > the plan which is finally returned inside pg_plan_query(). > > Look in createplan.c --- there are a couple places in there you need to > fix. > > regards, tom lane >
Tom, Thanks a ton again, and, here's another problem that has me really puzzled-<br /><br />I'm starting with a fresh
installof pgsql-8.0.1, and make 3 changes-<br /><br />1.) src/include/nodes/relation.h, Add a new Variable,
hutz_idx_benefitto IndexOptInfo <br /><br />typedef struct IndexOptInfo<br />{..............<br />/* Per IndexScan
benefit,More than 1 indexscan maybe used for 1 tablescan ex. w/ OR */<br /><span style="color: rgb(255, 0,
0);">Cost hutz_idx_benefit;</span><br/>..............} IndexOptInfo; <br /><br /><br />2.)
src/backend/optimizer/path/costsize.c,cost_index(), assign value to index->hutz_idx_benefit<br /><br />run_cost +=
indexTotalCost- indexStartupCost;<br /><span style="color: rgb(255, 0, 0);">index->hutz_idx_benefit = run_cost;
</span><br/><span style="color: rgb(51, 51, 255);">elog(NOTICE,"cost_index():index->indexoid=%u
index->hutz_idx_benefit=%.2f",index->indexoid, index->hutz_idx_benefit);</span><br /><br /><br />3.)
src/backend/optimizer/path/orindxpath.c,best_or_subclause_indexes(), Read the value(s) of index->indexoid and
index->hutz_idx_benefit<br/><br />/* Gather info for each OR subclause */<br />foreach(slist, subclauses)<br
/>{...................<br/>infos = lappend(infos, best_indexinfo);<br />...................} <br /><br />/<span
style="color:rgb(255, 0, 0);">* DEBUG */</span><br style="color: rgb(255, 0, 0);" /><span style="color: rgb(255, 0,
0);">ListCell *l;</span><br style="color: rgb(255, 0, 0);" /><span style="color: rgb(255, 0, 0);"> int
count=0;</span><brstyle="color: rgb(255, 0, 0);" /><span style="color: rgb(255, 0, 0);">foreach(l, infos)</span><br
style="color:rgb(255, 0, 0);" /><span style="color: rgb(255, 0, 0);">{</span><br style="color: rgb(255, 0, 0);" /><span
style="color:rgb(255, 0, 0);">IndexOptInfo *index = (IndexOptInfo *) lfirst(l);</span><br style="color: rgb(255, 0,
0);"/><span style="color: rgb(51, 51, 255);">elog(NOTICE,"best_or_subclause_indexes():infos c=%i: indexoid=%u
hutz_idx_benefit=%.2f",count, index->indexoid, index->hutz_idx_benefit);</span><br style="color: rgb(255, 0, 0);"
/><spanstyle="color: rgb(255, 0, 0);">count++;</span><br style="color: rgb(255, 0, 0);" /><span style="color: rgb(255,
0,0);">}</span><br />................... <br /> pathnode->indexinfo = infos; /* indexinfo' is a list of IndexOptInfo
nodes,one per scan to be performed */<br /><br /><br />So, basically I have added a new variable alongside indexoid
whichis the run_cost of one of the index scans if there are multiple index scans such as in the case of OR subclauses
for1 table.<br /> Now, I do a complete build and run two queries with OR subclauses as follows-<br /><br /> tpcd=#
selects_suppkey from supplier where (s_suppkey>125 and s_suppkey<128) or (s_suppkey>175 and s_suppkey<185)
or(s_suppkey>200 and s_suppkey<215);<br /> NOTICE: cost_index():index->indexoid=186970
index->hutz_idx_benefit=2.02<br/> NOTICE: cost_index():index->indexoid=186970 index->hutz_idx_benefit=2.06<br
/>NOTICE: cost_index():index->indexoid=186970 index->hutz_idx_benefit=<span style="color: rgb(204, 0, 0);
font-weight:bold;">2.09</span><br /> NOTICE: best_or_subclause_indexes():infos c=0: indexoid=186970
hutz_idx_benefit=<spanstyle="font-weight: bold; color: rgb(204, 0, 0);">2.09</span><br /> NOTICE:
best_or_subclause_indexes():infosc=1: indexoid=186970 hutz_idx_benefit=<span style="font-weight: bold; color: rgb(204,
0,0);">2.09</span><br /> NOTICE: best_or_subclause_indexes():infos c=2: indexoid=186970 hutz_idx_benefit=<span
style="font-weight:bold; color: rgb(204, 0, 0);">2.09</span><br /><br />On the second occasion, I change the order of
theOR subclauses...<br /><br /> tpcd=# select s_suppkey from supplier where (s_suppkey>200 and s_suppkey<215) or
(s_suppkey>175and s_suppkey<185) or (s_suppkey>125 and s_suppkey<128);<br /> NOTICE:
cost_index():index->indexoid=186970index->hutz_idx_benefit=2.09<br /> NOTICE:
cost_index():index->indexoid=186970index->hutz_idx_benefit=2.06<br /> NOTICE:
cost_index():index->indexoid=186970index->hutz_idx_benefit=<span style="font-weight: bold; color: rgb(204, 0,
0);">2.02</span><br/> NOTICE: best_or_subclause_indexes():infos c=0: indexoid=186970 hutz_idx_benefit=<span
style="font-weight:bold; color: rgb(204, 0, 0);">2.02</span><br /> NOTICE: best_or_subclause_indexes():infos c=1:
indexoid=186970hutz_idx_benefit=<span style="font-weight: bold; color: rgb(204, 0, 0);">2.02</span><br /> NOTICE:
best_or_subclause_indexes():infosc=2: indexoid=186970 hutz_idx_benefit=<span style="font-weight: bold; color: rgb(204,
0,0);">2.02</span><br /><br /><br /> From the output, it can be seen that when I try to read the value(s), the last
valueis stored in all the positions of the List "infos" which is later assigned to "(IndexPath) pathnode->indexinfo"
whichis a List of "IndexOptInfo" nodes, one per scan to be performed. Actually, it seems all the pointers in the List
"indexinfo"or "infos" are pointing to the same object.<br /><br /> So, <br /> Ques 1) Is my assumption correct that
IndexPath->indexinfoshould contain all distinct IndexOptInfo structs with one for each of the scans to be performed?
Ifnot, then why do we have multiple pointers to the same object?<br /><br /> (Ques 2) How can this be fixed? Is this a
bugor something else?<br /><br /> (Ques 3) Is this a problem in other areas as well, for example the following query
doesn'tgive the expected values as well-<br /> select s_suppkey, c_custkey from supplier, customer where
s_suppkey>125and s_suppkey<128 and c_custkey>125 and c_custkey<135 and c_custkey=s_suppkey;<br /><br /> I
appreciateall the help of this group,<br /> Thanks,<br /><br /><br />On 5/25/05, Tom Lane <<a
href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>>wrote:<br />> Vikram Kalsi <<a
href="mailto:vikramkalsi@gmail.com">vikramkalsi@gmail.com</a>>writes:<br />> > So, I suppose that during the
queryplanning and optimization stage, <br />> > the value of the original variables in the plan are somehow
copiedto<br />> > the plan which is finally returned inside pg_plan_query().<br />> <br />> Look in
createplan.c--- there are a couple places in there you need to <br />> fix.<br />> <br
/>> regards, tom lane<br />> <br />
On Thursday 02 June 2005 18:52, Vikram Kalsi wrote: > I am a MSEE student at Penn State (University Park), for the past few > months I have been working on modifying parts of PostgreSQL for my > research work. I doubt if my current work would serve any purpose for > pgsql since it is experimental and research oriented, but all the > same, I have gained familiarity with parts of pgsql. I'm interested in > Google's Summer of Code, but I would definitely need help, starting > with selection of an idea to work on. So, if anybody from PostgreSQL > would like to support this, then please get in touch with me as early > as possible. I think the easyist thing is to look over the TODO list and see if there are 1 or more items that you might be interested in working on, and then either mentioning them here or submitting them to google. > > By the way, I didn't see PostgreSQL in the list of Participating > Organizations on http://code.google.com/summerofcode.html? > Marc has contacted google with intentions of joining that list, I would guess the more people who submit postgresql related proposals, the more likely it would be that we end up joining. > Thanks and Regards, > -Vikram Kalsi > MSEE PennState > vzk101 at psu dot edu > www.personal.psu.edu/vzk101 > > On 5/25/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Vikram Kalsi <vikramkalsi@gmail.com> writes: > > > So, I suppose that during the query planning and optimization stage, > > > the value of the original variables in the plan are somehow copied to > > > the plan which is finally returned inside pg_plan_query(). > > > > Look in createplan.c --- there are a couple places in there you need to > > fix. > > > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Vikram Kalsi <vikramkalsi@gmail.com> writes:
> ...
> tpcd=# select s_suppkey from supplier where (s_suppkey>125 and
> s_suppkey<128) or (s_suppkey>175 and s_suppkey<185) or (s_suppkey>200 and
> s_suppkey<215);
> ...
> Actually, it seems all
> the pointers in the List "indexinfo" or "infos" are pointing to the same
> object.
Given that query, it's not too surprising that the only relevant index
for all the OR clauses would be the one on s_suppkey ... if you want to
look at *all* the indexes on the relation, you need to scan the parent
RelOptInfo's indexlist.
You didn't say what it was you hoped to accomplish, but perhaps the
technique requires a more complicated query to be of any use?
BTW, best_or_subclause_indexes (and indeed most of orindxpath.c) is
gone in CVS tip; all that code has been rewritten for 8.1.
regards, tom lane