Thick Index Design: Thick Index means index with snapshot information. It stores the snapshot information together with the IndexTuple. This would turn out to be useful, since the normal index scans need not refer to the heap for checking the heap tuple's visibility. This would also provide a new set of scans which will satisfy the complete query from Index. It will act like a mini Index Organized Table. This benefit comes at a cost, where in we need to update the index tuples, whenever there is an update / delete on the HeapTuple. Currently Thick indexes do not support Functional indexes. Index Tuple Structure for thick index occupies 16 more bytes. ( Xmin - 4Bytes, Xmax - 4Bytes, Cid - 4 Bytes, t_infomask - 4 Bytes). In the t_infomask, it mainly uses the bits like Xmin_Committed, Xmin_invalid etc. This 16 Byte field is called SnapshotFieldsData. Also there is a new column added into pg_index called 'indhassnapshot'. This field, if true indicates the index being referred is Thick index The IndexScanDescData structure contains a pointer called SnapshotIndexParams. This structure contains various information, which can be used by a thick index to speed up queries of certain class. + typedef struct SnapshotIndexParamsData + { + bool isIndexOnlyScan; + bool is_minmax_scan; + bool is_count_only_scan; + bool cache_stack; + CmdType operation; + HeapTuple htup; + ScanKey insertion_skey; /* Insertion Scan Key */ + void* stack; + TupleTableSlot* slot; + } SnapshotIndexParamsData; a) isIndexOnlyScan - Whether the scan need to goto the table to fetch extra attributes. If this is true, then thick index scan would start caching the index tuples. Currently the index tuples are being cached under the structure MinimalIndexTupleData + typedef struct MinimalIndexTupleData + { + bool has_null; + } MinimalIndexTupleData; While this might provide some benefit for 32 bit systems, the usefulness of this structure for 64 bit systems is questionable. b) is_minmax_scan - This scan indicates that the scan can end as soon as it finds the first live tuple. c) is_count_only_scan - This scan provides an optimization, using which we need not store the MinimalIndexTuple multiple times. Instead we just store a static all null tuple once and use the count which is already present in the IndexScanDesc d) cache_stack - This is another optimization for updates involving thick indexes. The update operation involves a delete and insert. Since we already find out the leaf node where the tuple is present, we can insert the new tuple at the same place. This cache_stack gets turned on, when we find out that the attributes involving the thick index are not updated. e) operation - informs whether it is a SELECT, INSERT, DELETE etc. f) htup - the heap tuple being indexed. This is useful while we are deleting a thick index tuple. It would refer to the heap tuple for transaction snapshot information.This actually points to the tuple-tableslot where the old heap tuple is cached g) insertion_skey - this is useful for the delete operation to delete the old tuple. h) stack - refer to the cache_stack description i) slot - This is a pointer to the slot being used on a index scan As pointed above the Minimal Index Tuple data has to get cached before we leave the index page. It gets cached under the BTScanOpaque structure under the name index_cache Function ExecDeleteIndexTuples is added for thick indexes to delete its index entry. It retraces the index tuple by forming a search scan key on the index tuple fields. ExecUpdateIndexTuples has also got updated to provide the necessary functionality for Thick index. Vacuums: Currently we store all the heap-tids which can be vacuumed in the heap tuple and we goto index and we scan the complete index and delete all the corresponding index tuples. with thick index, all the information necessary for Vacuum is already present in the Index. So the Tid array is not required. It just scans through the index and vacuums all the tuples. Actually Vacuum is not at all required for thick index, as we do a one page btree vacuum whenever necessary. But Freezing makes Vacuum necessary for thick indexes. Also If a table contains only thick indexes, then tid array need not be created. This would make the table equivalent to a table with no indexes. Pinning the index page: Currently the heap tuple can't be cleaned up before ensuring all the index tuples are cleaned up. since we don't want any index scans to be using the index tuples while we are vacuuming the index page, we hold a pin. This basically means that the index page can't be vacuumed, which inturn means that the heap tuple being referred can't be vacuumed. But in case of thick indexes, the heap tuple can be vacuumed even while the index tuple exists, because the index tuple won't be considered by the index scan, if the tuple satisfies vacuum. Once a scan finds that the tuple satisfies vacuum, it is marked as LP_DEAD. So there is no concept of Super Exclusive Lock with Thick indexes. A new recovery function has been written for updating the snapshot information during the heap tuple delete. This puts the necessary WAL information for recovery. Cost Calculation: There is a function called IndexOnlyScan which finds out whether the current scan can become a index only scan. If it becomes a Indexonlyscan, then the index scan is divided by a factor called DEFAULT_INDEX_ONLY_SCAN_FACTOR, which is currently set at 4. For Bitmap Or scans, there are no index scans generated. Currently IndexOnlyScan is not supported for Bitmap Or scans. Since a MinimalIndexTuple cache gets created, the management of cache and finding out the duplicates inside the two or more scans present with 'Or' scan requires some more thinking. There is a new function written to provide the Search scan key and Insertion scan key from the heap tuples. Search scan Key is using the oper_select_candidate function, which might run slightly faster with 8.3 Beta 4. All my presented performance test results in the earlier threads were done on 8.3 Beta 1. All the HOT stuffs are skipped for Thick index scans, as HOT won't get applied to thick indexes. Explain Plans: If IndexOnlyScan is chosen with a thick index, it gets notified in the Explain and Explain analyze statements. Index Creation: Create thick index ... should be used in order to create a thick index on top of a column.