Doubt in IndexScanDescData

[ "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;

---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

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.

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

Hans-Juergen Schoenig <postgres(at)cybertec(dot)at> wrote:
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

---------------------------------
Never miss a thing. Make Yahoo your homepage.

take a look at that ...

http://www.postgresql.org/docs/8.3/static/indexam.html

this might clear up the problem.
here is an example making clear what happens:

select phone_number from phonebook where name = 'xy';

index is asked to find the right place in the heap to retrieve the data.
this is what happens during an index scan.
i suggest to step tnrough this process with a debugger to see what is
going on.

hans

On Feb 17, 2008, at 5:13 PM, Suresh wrote:

> Hans-Juergen Schoenig <postgres(at)cybertec(dot)at> wrote:
>
> 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
>
>
>
>
> Never miss a thing. Make Yahoo your homepage.

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

What do the following lines mean :

/* Tuple failed time qual, but maybe caller wants to see it anyway. */
if (keep_buf)
*userbuf = buffer;
else
{
ReleaseBuffer(buffer);
*userbuf = InvalidBuffer;
}

What is the time qualification check ?

Thanks,
Suresh

---------------------------------
Never miss a thing. Make Yahoo your homepage.

Suresh <suiyengar(at)yahoo(dot)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,

I have a custom code written inside postgres in an application we use.
The snippet is as below :
Here inner plan is an index scan.

scandesc = ((IndexScanState *)innerPlan)->iss_ScanDesc;

flag=index_getmulti(scandesc, &tidelm->tid, 1, &ret_tids);

Now consider a query like

explain select * from dept,manager where did=id ;
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop (cost=0.00..269.09 rows=45 width=72)
-> seq scan on manager (cost=0.00..6.50 rows=45 width=36)
-> Index Scan using id1 on dept (cost=0.00..5.82 rows=1 width=36)
Index Cond: ("outer".did = dept.id)

Say seq scan retrieves did in the order 30,10, 20.. My doubt is in what order
will index_getmulti return tids. How does the scandesc work ?

Will it return the tids as firstly macthing inners for dept=30, then dept=10 ?

Please help me with this.

Thanks and regards,
Suresh

---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

On 8-Mar-08, at 11:06 AM, Suresh wrote:

> Hello all,
>
> I have a custom code written inside postgres in an application we use.
> The snippet is as below :
> Here inner plan is an index scan.
>
> scandesc = ((IndexScanState *)innerPlan)->iss_ScanDesc;
>
> flag=index_getmulti(scandesc, &tidelm->tid, 1, &ret_tids);
>
> Now consider a query like
>
> explain select * from dept,manager where did=id ;
> QUERY PLAN
> ---------------------------------------------------------------------------
> Nested Loop (cost=0.00..269.09 rows=45 width=72)
> -> seq scan on manager (cost=0.00..6.50 rows=45 width=36)
> -> Index Scan using id1 on dept (cost=0.00..5.82 rows=1 width=36)
> Index Cond: ("outer".did = dept.id)
>
> Say seq scan retrieves did in the order 30,10, 20.. My doubt is in
> what order
> will index_getmulti return tids. How does the scandesc work ?
>
> Will it return the tids as firstly macthing inners for dept=30, then
> dept=10 ?
>
since you have no order by clause in the query rows will be returned
in the order they are found on the disc.

Dave
> Please help me with this.
>
> Thanks and regards,
> Suresh
>
>
>
>
>
>
>
>
>
>
>
> Be a better friend, newshound, and know-it-all with Yahoo! Mobile.
> Try it now.

Hello,

I have a custom code in postgres which runs properly in some occasions and segfaults some times. The trace is as below :

Program received signal SIGSEGV, Segmentation fault.
0x081ae8c4 in LWLockRelease (lockid=664)
at ../../../../src/include/storage/s_lock.h:128
128 __asm__ __volatile__(

(gdb) where
#0 0x081ae8c4 in LWLockRelease (lockid=664)
at ../../../../src/include/storage/s_lock.h:128
#1 0x0808f820 in heap_fetch_tuple (relation=0xb5d986d8, snapshot=0xa298aa0,
buffer=305, tid=0xa23f600, tuple=0xa29db0c, pgstat_info=0xa29db30,
tupStat=0xbfac9374) at heapam.c:3404
#2 0x08144df2 in ExecNestLoop (node=0xa298f30) at nodeNestloop.c:452
#3 0x08136840 in ExecProcNode (node=0xa298f30) at execProcnode.c:352
#4 0x08135ba1 in ExecutorRun (queryDesc=0xa298ac8,
direction=ForwardScanDirection, count=0) at execMain.c:1162
#5 0x081b7e60 in PortalRunSelect (portal=0xa296a98,
forward=<value optimized out>, count=0, dest=0x82d3308) at pquery.c:794
#6 0x081b8a88 in PortalRun (portal=0xa296a98, count=2147483647,
dest=0x82d3308, altdest=0x82d3308, completionTag=0xbfac9608 "")
at pquery.c:646
#7 0x081b48fc in exec_simple_query (
query_string=0xa275b58 "select l_orderkey as a from tpcd.orders, tpcd.lineitem where o_orderkey=l_orderkey ;\n") at postgres.c:1003
#8 0x081b6371 in PostgresMain (argc=1, argv=0xa2379f0,
username=0xa238398 "suresh") at postgres.c:3221
#9 0x081532e3 in main (argc=2, argv=Cannot access memory at address 0xfffffffd
) at main.c:411

It segfaults in the locking _asm_ code. What could be the reason for this variable behavior ?

Thanks and regards,
Suresh

---------------------------------
Never miss a thing. Make Yahoo your homepage.

Suresh <suiyengar(at)yahoo(dot)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,

I want to collect various statistics like time taken, number of context switches, page faults etc.. for a query being run. postgres.c contains lots of getrusage related things.

Is there any way to calculate all the things without writing any custom code ?

Thanks and regards,
Suresh

---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

Hello,

I have custom postgres code. I get the error below for the query

"select l_orderkey as a from tpcd.orders, tpcd.lineitem where o_orderkey=l_orderkey and l_partkey<100 and l_linestatus='F';"

ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth".

However, the same code runs fine with one condition in where clause, but fails with the error above in case of multiple conditions.

Whats the cause of this error ? I tried increasing the stack limit; but it doesnt help.

--
Suresh Iyengar

---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

"Suresh" <suiyengar(at)yahoo(dot)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,

The code uses Asynchronous I/O for fetching certain tids. The code works fine if I use only one condition in where condition, but fails if I use multiple condition.

--
Suresh Iyengar

Gregory Stark <stark(at)enterprisedb(dot)com> wrote: "Suresh" 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!

---------------------------------
Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

Hello,

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 ?

Thanks and regards,
Suresh

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(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Hello,

Why is check_stack_depth function enforced in context of evaluating expressions in PostgreSQL ? What sort of recursion we are trying to safeguard ?

thanks,
Suresh

Suresh <suiyengar(at)yahoo(dot)com> writes:
> Why is check_stack_depth function enforced in context of evaluating expressions in PostgreSQL ? What sort of recursion we are trying to safeguard ?

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

select foo(1);

regards, tom lane