SEO

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, bool is_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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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
>

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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
>

---------------------------(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

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 */
Cost hutz_idx_benefit;
...............} IndexOptInfo;


2.) src/backend/optimizer/path/costsize.c, cost_index(), assign value to
index->hutz_idx_benefit

run_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_suppkey>125 and
s_suppkey<128) or (s_suppkey>175 and s_suppkey<185) or (s_suppkey>200 and
s_suppkey<215);
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_suppkey>200 and
s_suppkey<215) or (s_suppkey>175 and s_suppkey<185) or (s_suppkey>125 and
s_suppkey<128);
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_suppkey>125 and
s_suppkey<128 and c_custkey>125 and c_custkey<135 and c_custkey=s_suppkey;

I appreciate all the help of this group,
Thanks,


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
>

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster