Re: [HACKERS] Source Code Help Needed
Tom, Thanks a ton again, and, here's another problem that has me really puzzled-I'm starting with a fresh install of pgsql-8.0.1, and make 3 changes-1.) src/include/nodes/relation.h, Add a new Variable, hutz_idx_benefit to IndexOptInfo
typedef struct IndexOptInfo{../* Per IndexScan benefit, More than 1 indexscan maybe used for 1 tablescan ex. w/ OR */Costhutz_idx_benefit;..} IndexOptInfo;
2.) src/backend/optimizer/path/costsize.c, cost_index(), assign value to index-hutz_idx_benefitrun_cost += indexTotalCost - indexStartupCost;index-hutz_idx_benefit = run_cost;
elog(NOTICE,cost_index():index-indexoid=%u
index-hutz_idx_benefit=%.2f, index-indexoid,
index-hutz_idx_benefit);3.)
src/backend/optimizer/path/orindxpath.c, best_or_subclause_indexes(),
Read the value(s) of index-indexoid and index-hutz_idx_benefit/* Gather info for each OR subclause */foreach(slist, subclauses){...infos = lappend(infos, best_indexinfo);...}
/* DEBUG */ListCell *l;
int count=0;foreach(l, infos){
IndexOptInfo *index = (IndexOptInfo *) lfirst(l);elog(NOTICE,best_or_subclause_indexes():infos
c=%i: indexoid=%u hutz_idx_benefit=%.2f, count, index-indexoid,
index-hutz_idx_benefit);count++;}...
pathnode-indexinfo = infos; /* indexinfo' is a list of IndexOptInfo nodes, one per scan to be performed */
So, basically I have added a new variable alongside indexoid which
is the run_cost of one of the index scans if there are multiple index
scans such as in the case of OR subclauses for 1 table.
Now, I do a complete build and run two queries with OR subclauses as follows-
tpcd=# select s_suppkey from supplier where (s_suppkey125 and
s_suppkey128) or (s_suppkey175 and s_suppkey185) or
(s_suppkey200 and s_suppkey215);
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.02
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.06
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.09
NOTICE: best_or_subclause_indexes():infos c=0: indexoid=186970 hutz_idx_benefit=2.09
NOTICE: best_or_subclause_indexes():infos c=1: indexoid=186970 hutz_idx_benefit=2.09
NOTICE: best_or_subclause_indexes():infos c=2: indexoid=186970 hutz_idx_benefit=2.09
On the second occasion, I change the order of the OR subclauses...
tpcd=# select s_suppkey from supplier where (s_suppkey200 and
s_suppkey215) or (s_suppkey175 and s_suppkey185) or
(s_suppkey125 and s_suppkey128);
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.09
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.06
NOTICE: cost_index():index-indexoid=186970 index-hutz_idx_benefit=2.02
NOTICE: best_or_subclause_indexes():infos c=0: indexoid=186970 hutz_idx_benefit=2.02
NOTICE: best_or_subclause_indexes():infos c=1: indexoid=186970 hutz_idx_benefit=2.02
NOTICE: best_or_subclause_indexes():infos c=2: indexoid=186970 hutz_idx_benefit=2.02
>From the output, it can be seen that when I try to read the value(s),
the last value is stored in all the positions of the List infos which
is later assigned to (IndexPath) pathnode-indexinfo which is 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.
So,
Ques 1) Is my assumption correct that IndexPath-indexinfo should
contain all distinct IndexOptInfo structs with one for each of the
scans to be performed? If not, then why do we have multiple pointers to
the same object?
(Ques 2) How can this be fixed? Is this a bug or something else?
(Ques 3) Is this a problem in other areas as well, for example the following query doesn't give the expected values as well-
select s_suppkey, c_custkey from supplier, customer where
s_suppkey125 and s_suppkey128 and c_custkey125 and
c_custkey135 and c_custkey=s_suppkey;
I appreciate all the help of this group,
Thanks,
On 5/25/05, Tom Lane [EMAIL PROTECTED] wrote: Vikram Kalsi [EMAIL PROTECTED] 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
Re: [HACKERS] Source Code Help Needed
Vikram Kalsi [EMAIL PROTECTED] writes: ... tpcd=# select s_suppkey from supplier where (s_suppkey125 and s_suppkey128) or (s_suppkey175 and s_suppkey185) or (s_suppkey200 and s_suppkey215); ... 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 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
{
Costhutz_tbl_benefit; /* Benefit for TableAccess */
Costhutz_idx_benefit; /* Benefit for IndexScan */
}
2.) New Variables ADDED to src/include/nodes/relation.h
typedef struct Plan
{
Costhutz_tbl_benefit; /* Benefit for TableAccess */
Costhutz_idx_benefit; /* Benefit for IndexScan */
}
3.) ADDITIONS to costsize.c
void cost_seqscan(Path *path, Query *root,
RelOptInfo *baserel)
{
path-hutz_tbl_benefit = x;
path-hutz_idx_benefit = x;
}
void cost_index(Path *path, Query *root, RelOptInfo *baserel,
IndexOptInfo *index, List *indexQuals, bool is_injoin)
{
path-hutz_tbl_benefit = x;
path-hutz_idx_benefit = x;
}
However, after these modifications the server process crashes on
running a Join query like
select s_suppkey,c_custkey from supplier,customer where s_suppkey125
and s_suppkey128 and c_custkey100 and c_custkey103 and
c_custkey=s_suppkey
But, this query runs fine select s_suppkey from supplier where
s_suppkey125 and s_suppkey128
I'm tracing the point at which the process crashes and at this point
it seems to inside
src/backend/optimizer/path/joinrels.cmake_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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Source Code Help Needed
Vikram Kalsi [EMAIL PROTECTED] 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_suppkey125 and s_suppkey128 and c_custkey100 and c_custkey103 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Source Code Help Needed
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 [EMAIL PROTECTED] wrote: Vikram Kalsi [EMAIL PROTECTED] 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_suppkey125 and s_suppkey128 and c_custkey100 and c_custkey103 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Source Code Help Needed
Vikram Kalsi [EMAIL PROTECTED] 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
