Doubt in IndexScanDescData

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@cybertec.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@cybertec.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@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,

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

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

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

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@enterprisedb.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@decibel.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@yahoo.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