This fixes the bug in dealing with scans with 'or' conditions. I have also
attached the design document.


On Jan 16, 2008 7:25 PM, Gokulakannan Somasundaram <[EMAIL PROTECTED]>

> Hi,
>     I did some more bug fixes and performance updates especially for
> select count(1) queries.
> Thanks,
> Gokul.
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 

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

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.

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 

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.

Attachment: patchfile.tar.gz
Description: GNU Zip compressed data

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly

Reply via email to