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