Обсуждение: Which data structures for the index?

On 18.11.2010 15:19, Vaibhav Kaushal wrote:
> I have a small problem: Suppose that I have a table in PostgreSQL which has
> a integer field as its Primary key and I have used the Hash indexing method
> for creating the index for the field on the table. Now I want to know the
> following details about the index (assuming the machine running PostgreSQL
> is a Linux box with DB installed at /var/lib/pgsql/data):
>
> * Which file would contain the index table data? (OR how to find them? Will
> I find them in the CATALOG tables?)

SELECT relfilenode FROM pg_class WHERE relname='index name'. The index 
data is stored in a file with that name. Something like 
/var/lib/pgsql/data/base/11910/<relfilenode from that query>

> * Is there some documentation available about the source apart from that on
> the website and the the one which gets compiled with the source? (specially
> about the conversions and the steps of conversion of the data from they raw
> disc reads to the structured layout in the memory just before the executor
> is started)

The source and the README files in the source tree are your best help. 
And the comments in the header files are very helpful too.

> * Which file in the source tree is responsible for the scan of the index?
> (The main file in case there are two of them for the btree and hash indexes
> separately)

src/backend/access/nbtree/nbtree.c, btgettuple function
and
src/backend/access/hash/hash.c, hashgettuple function

src/backend/access/index/indexam.c is the common entry point for all 
index types.

> * Which data structures are the most important ones in index scanning? (I
> will search them myself but please someone tell me the structures; there are
> just too many of them :( )

Depends on what you're interested in. IndexScanDesc is common between 
all index scans, Understanding the page structure might also be helpful, 
see src/include/storage/bufpage.h.

> * Are the pages of the DB file layout of the index table in someway
> different than what is discussed at
> http://www.postgresql.org/docs/9.0/interactive/storage-file-layout.html ? If
> yes then what are the differences?

No, that applies to indexes too.

> And I must say that while browsing the source, I was so pleased to read the
> comments (they really helped a lot). Thanks to the PostgreSQL coding
> conventions and of course the contributors. I am a bit clear about the
> working of the executor (thanks to ECLIPSE for support of ctags and a nice
> UI) but I am still much in a mess.
>
> Thanks in advance for the answer ;)

Good luck!

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

Thanks once again Mr. Heikki for your help. Got your point! <br /><br />Thanks :)<br /><br />-Vaibhav (*_*)<br /><br
/><divclass="gmail_quote">On Thu, Nov 18, 2010 at 7:10 PM, Heikki Linnakangas <span dir="ltr"><<a
href="mailto:heikki.linnakangas@enterprisedb.com">heikki.linnakangas@enterprisedb.com</a>></span>wrote:<br
/><blockquoteclass="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left:1ex;"><div class="im">On 18.11.2010 15:19, Vaibhav Kaushal wrote:<br /><blockquote class="gmail_quote"
style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> I have a small
problem:Suppose that I have a table in PostgreSQL which has<br /> a integer field as its Primary key and I have used
theHash indexing method<br /> for creating the index for the field on the table. Now I want to know the<br /> following
detailsabout the index (assuming the machine running PostgreSQL<br /> is a Linux box with DB installed at
/var/lib/pgsql/data):<br/><br /> * Which file would contain the index table data? (OR how to find them? Will<br /> I
findthem in the CATALOG tables?)<br /></blockquote><br /></div> SELECT relfilenode FROM pg_class WHERE relname='index
name'.The index data is stored in a file with that name. Something like /var/lib/pgsql/data/base/11910/<relfilenode
fromthat query><div class="im"><br /><br /><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex;
border-left:1px solid rgb(204, 204, 204); padding-left: 1ex;"> * Is there some documentation available about the source
apartfrom that on<br /> the website and the the one which gets compiled with the source? (specially<br /> about the
conversionsand the steps of conversion of the data from they raw<br /> disc reads to the structured layout in the
memoryjust before the executor<br /> is started)<br /></blockquote><br /></div> The source and the README files in the
sourcetree are your best help. And the comments in the header files are very helpful too.<div class="im"><br /><br
/><blockquoteclass="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left:1ex;"> * Which file in the source tree is responsible for the scan of the index?<br /> (The main file in
casethere are two of them for the btree and hash indexes<br /> separately)<br /></blockquote><br /></div>
src/backend/access/nbtree/nbtree.c,btgettuple function<br /> and<br /> src/backend/access/hash/hash.c, hashgettuple
function<br/><br /> src/backend/access/index/indexam.c is the common entry point for all index types.<div
class="im"><br/><br /><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204,
204,204); padding-left: 1ex;"> * Which data structures are the most important ones in index scanning? (I<br /> will
searchthem myself but please someone tell me the structures; there are<br /> just too many of them :( )<br
/></blockquote><br/></div> Depends on what you're interested in. IndexScanDesc is common between all index scans,
Understandingthe page structure might also be helpful, see src/include/storage/bufpage.h.<div class="im"><br /><br
/><blockquoteclass="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left:1ex;"> * Are the pages of the DB file layout of the index table in someway<br /> different than what is
discussedat<br /><a href="http://www.postgresql.org/docs/9.0/interactive/storage-file-layout.html"
target="_blank">http://www.postgresql.org/docs/9.0/interactive/storage-file-layout.html</a>? If<br /> yes then what are
thedifferences?<br /></blockquote><br /></div> No, that applies to indexes too.<div class="im"><br /><br /><blockquote
class="gmail_quote"style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
AndI must say that while browsing the source, I was so pleased to read the<br /> comments (they really helped a lot).
Thanksto the PostgreSQL coding<br /> conventions and of course the contributors. I am a bit clear about the<br />
workingof the executor (thanks to ECLIPSE for support of ctags and a nice<br /> UI) but I am still much in a mess.<br
/><br/> Thanks in advance for the answer ;)<br /></blockquote><br /></div> Good luck!<br /><font color="#888888"><br />
--<br />  Heikki Linnakangas<br />  EnterpriseDB   <a href="http://www.enterprisedb.com"
target="_blank">http://www.enterprisedb.com</a><br/></font></blockquote></div><br />