Обсуждение: Doubt in IndexScanDescData

[ "include/access/relscan.h" ]<br /><br />In  IndexScanDescData,  whats the purpose of having two Relation
variables.<br/><br />typedef struct IndexScanDescData<br />{<br />        Relation        heapRelation;   /* heap
relationdescriptor, or NULL */<br />        Relation        indexRelation;  /* index relation descriptor */<br
/>....<br/>...<br />}IndexScanDescData;<br /><br /><br /><p><hr size="1" />Be a better friend, newshound, and
know-it-allwith Yahoo! Mobile. <a
href="http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ"> Try it now.</a> 

On Feb 17, 2008, at 4:33 PM, Suresh wrote:

[ "include/access/relscan.h" ]

In  IndexScanDescData,  whats the purpose of having two Relation variables.

typedef struct IndexScanDescData
{
        Relation        heapRelation;   /* heap relation descriptor, or NULL */
        Relation        indexRelation;  /* index relation descriptor */
....
...
}IndexScanDescData;

The index does not contain the entire tuple. If you index column A the index will not contain values in column B of the same table.

Thus, if you find a record in the index one of the things which have to be done is to get the record from disk to check visibility and other columns.

Yes thats correct. But I still dont get it. To get record from the disk on match, we need Relation data. But whats the purpose having two seperate Relation variables ?

Does it mean that heaprelation will contain only info about that particular column of the table and index relation will have info about the whole tuple of the relation ?


best regards,

hans-juergen schoenig

--

Cybertec Schönig & Schönig GmbH

PostgreSQL Solutions and Support

Gröhrmühlgasse 26, 2700 Wiener Neustadt

Tel: +43/1/205 10 35 / 340

www.postgresql.at, www.cybertec.at

One thing you might be missing is that indexes are relations too. They're a
bit different than heap relations (ie "tables") but they share enough
structure that it's worth using the same datatypes to represent both.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!

Hello,<br /><br />What do the following lines mean :<br /><br />  /* Tuple failed time qual, but maybe caller wants to
seeit anyway. */<br />        if (keep_buf)<br />                *userbuf = buffer;<br />        else<br />        {<br
/>               ReleaseBuffer(buffer);<br />                *userbuf = InvalidBuffer;<br />        }<br /><br />What
isthe time qualification check ?<br /><br />Thanks,<br />Suresh<br /><br /><br /><p><hr size="1" />Never miss a thing.
<ahref="http://us.rd.yahoo.com/evt=51438/*http://www.yahoo.com/r/hs"> Make Yahoo your homepage.</a> 

Suresh <suiyengar@yahoo.com> writes:
> What is the time qualification check ?

HeapTupleSatisfiesVisibility().  See
src/include/utils/tqual.h
src/backend/utils/time/tqual.c
and if none of this is making any sense maybe you need to start here:
http://developer.postgresql.org/pgdocs/postgres/mvcc.html
        regards, tom lane

Hello all,<br /><br />I have a custom code written inside postgres in an application we use.<br />The snippet is as
below:<br />Here inner plan is an index scan.<br /><br />  scandesc = ((IndexScanState *)innerPlan)->iss_ScanDesc;
<br/><br />  flag=index_getmulti(scandesc, &tidelm->tid, 1, &ret_tids);<br /><br />Now consider a query
like<br/><br />explain  select * from dept,manager where did=id ;<br />                                QUERY
PLAN                                <br
/>---------------------------------------------------------------------------<br/> Nested Loop  (cost=0.00..269.09
rows=45width=72)<br />   -> seq scan on manager  (cost=0.00..6.50 rows=45 width=36)<br />   ->  Index Scan using
id1on dept  (cost=0.00..5.82 rows=1 width=36)<br />         Index Cond: ("outer".did = dept.id)<br /><br />Say seq scan
retrievesdid in the order 30,10, 20.. My doubt is in what order <br />will index_getmulti return tids. How does the
scandescwork ? <br /><br />Will it return the tids as firstly macthing inners for dept=30, then dept=10 ?<br /><br
/>Pleasehelp me with this.<br /><br />Thanks and regards,<br />Suresh<br /><br /><br /><br /><br /><br /><br /><br
/><br/><br /><br /><p><hr size="1" />Be a better friend, newshound, and know-it-all with Yahoo! Mobile. <a
href="http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ"> Try it now.</a> 

Hello,<br /><br />I have a custom code in postgres which runs properly in some occasions and segfaults some times. The
traceis as below :<br /><br />Program received signal SIGSEGV, Segmentation fault.<br />0x081ae8c4 in LWLockRelease
(lockid=664)<br/>    at ../../../../src/include/storage/s_lock.h:128<br />128             __asm__ __volatile__(<br
/><br/><br />(gdb) where<br />#0  0x081ae8c4 in LWLockRelease (lockid=664)<br />    at
../../../../src/include/storage/s_lock.h:128<br/>#1  0x0808f820 in heap_fetch_tuple (relation=0xb5d986d8,
snapshot=0xa298aa0,<br />    buffer=305, tid=0xa23f600, tuple=0xa29db0c, pgstat_info=0xa29db30, <br />   
tupStat=0xbfac9374)at heapam.c:3404<br />#2  0x08144df2 in ExecNestLoop (node=0xa298f30) at nodeNestloop.c:452<br />#3 
0x08136840in ExecProcNode (node=0xa298f30) at execProcnode.c:352<br />#4  0x08135ba1 in ExecutorRun
(queryDesc=0xa298ac8,<br />    direction=ForwardScanDirection, count=0) at execMain.c:1162<br />#5  0x081b7e60 in
PortalRunSelect(portal=0xa296a98, <br />    forward=<value optimized out>, count=0, dest=0x82d3308) at
pquery.c:794<br/>#6  0x081b8a88 in PortalRun (portal=0xa296a98, count=2147483647, <br />    dest=0x82d3308,
altdest=0x82d3308,completionTag=0xbfac9608 "")<br />    at pquery.c:646<br />#7  0x081b48fc in exec_simple_query (<br
/>   query_string=0xa275b58 "select l_orderkey as a from tpcd.orders, tpcd.lineitem where o_orderkey=l_orderkey ;\n")
atpostgres.c:1003<br />#8  0x081b6371 in PostgresMain (argc=1, argv=0xa2379f0, <br />    username=0xa238398 "suresh")
atpostgres.c:3221<br />#9  0x081532e3 in main (argc=2, argv=Cannot access memory at address 0xfffffffd<br />) at
main.c:411<br/><br />It segfaults in the locking _asm_ code. What could be the reason for this variable behavior ?<br
/><br/>Thanks and regards,<br />Suresh<br /><p><hr size="1" />Never miss a thing. <a
href="http://us.rd.yahoo.com/evt=51438/*http://www.yahoo.com/r/hs">Make Yahoo your homepage.</a> 

Suresh <suiyengar@yahoo.com> writes:
> I have a custom code in postgres which runs properly in some occasions and segfaults some times. The trace is as
below:
 

The traceback you show appears to lead through code that doesn't exist
in any public version of Postgres.  So I think it's your own bug to
solve.
        regards, tom lane

Hello,<br /><br />I want to collect various statistics like time taken, number of context switches, page faults etc..
fora query being run. postgres.c contains lots of getrusage related things.<br /><br />Is there any way to calculate
allthe things without writing any custom code ?<br /><br />Thanks and regards,<br />Suresh<br /><br /><p><hr size="1"
/>Bea better friend, newshound, and know-it-all with Yahoo! Mobile. <a
href="http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ"> Try it now.</a> 

Hello,<br /><br />I have <span style="font-weight: bold;">custom </span>postgres code. I get the error below for the
query<br /><br />"select l_orderkey as a from tpcd.orders, tpcd.lineitem where o_orderkey=l_orderkey and
l_partkey<100and l_linestatus='F';"<br /><br />ERROR:  stack depth limit exceeded<br />HINT:  Increase the
configurationparameter "max_stack_depth".<br /><br />However, the same code runs fine with one condition in where
clause,but fails with the error above in case of multiple conditions.<br /><br />Whats the cause of this error ? I
triedincreasing the stack limit; but it doesnt help.<br /><br />--<br />Suresh Iyengar<br /><p><hr size="1" />Be a
betterfriend, newshound, and know-it-all with Yahoo! Mobile. <a
href="http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ"> Try it now.</a> 

"Suresh" <suiyengar@yahoo.com> writes:

> Hello,
>
> I have custom postgres code. 

What kind of code is this? The error below is typical if you create new
threads in the server.


--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!

Hi,<br /><br />The code uses Asynchronous I/O for fetching certain tids. The code works fine if I use only one
conditionin where condition, but fails if I use multiple condition.<br /><br />--<br />Suresh Iyengar<br /><br
/><b><i>GregoryStark <stark@enterprisedb.com></i></b> wrote:<blockquote class="replbq" style="border-left: 2px
solidrgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"> "Suresh"  writes:<br /><br />> Hello,<br />><br
/>>I have custom postgres code. <br /><br />What kind of code is this? The error below is typical if you create
new<br/>threads in the server.<br /><br /><br />-- <br /> Gregory Stark<br /> EnterpriseDB
http://www.enterprisedb.com<br/> Ask me about EnterpriseDB's 24x7 Postgres support!<br /></blockquote><br /><p><hr
size="1"/>Be a better friend, newshound, and know-it-all with Yahoo! Mobile. <a
href="http://us.rd.yahoo.com/evt=51733/*http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ"> Try it now.</a> 

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Hello,<br /><br />I have
aquery plan for a certain query<br /><br /> Nested Loop  (cost=100000000.00..38761761090.50 rows=3000608 width=8)<br
/>  ->  Seq Scan on lineitem  (cost=100000000.00..100213649.15 rows=6001215 width=8)<br />   ->  Index Scan using
oindex2on myorders  (cost=0.00..6442.27 rows=1 width=4)<br />         Index Cond: ("outer".l_orderkey =
myorders.o_orderkey)<br/>         Filter: (subplan)<br />         SubPlan<br />           ->  Index Scan using
cnationon customer  (cost=0.00..12859.39 rows=5251 width=0)<br />                 Index Cond: (c_nationkey = 10)<br
/><br/>How is the subplan handled by postgres at index level ? Is any sort of hashing done ?<br /><br />Thanks and
regards,<br/>Suresh<br /><br /></td></tr></table><br /> 

On Jun 20, 2008, at 1:11 AM, Suresh wrote:
> I have a query plan for a certain query
>
>  Nested Loop  (cost=100000000.00..38761761090.50 rows=3000608 width=8)
>    ->  Seq Scan on lineitem  (cost=100000000.00..100213649.15  
> rows=6001215 width=8)
>    ->  Index Scan using oindex2 on myorders  (cost=0.00..6442.27  
> rows=1 width=4)
>          Index Cond: ("outer".l_orderkey = myorders.o_orderkey)
>          Filter: (subplan)
>          SubPlan
>            ->  Index Scan using cnation on customer   
> (cost=0.00..12859.39 rows=5251 width=0)
>                  Index Cond: (c_nationkey = 10)
>
> How is the subplan handled by postgres at index level ? Is any sort  
> of hashing done ?

This is better asked on pgsql-general... but the subplan does exactly  
what it says; an index scan. It will be executed for every row of the  
calling query.
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top">Hello,<br /><br />Why is
check_stack_depthfunction enforced in context of evaluating expressions in PostgreSQL ? What sort of recursion we are
tryingto safeguard ?<br /><br />thanks,<br />Suresh<br /><br /></td></tr></table><br /> 

Suresh <suiyengar@yahoo.com> writes:
> Why is check_stack_depth function enforced in context of evaluating expressions in PostgreSQL ? What sort of
recursionwe are trying to safeguard ?
 

create function foo(int) returns int as $$
select foo($1) $$ language sql;

select foo(1);
        regards, tom lane