codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>72.1. Overview</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="hash-index.html" title="Chapter 72. Hash Indexes" /><link rel="next" href="hash-implementation.html" title="72.2. Implementation" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">72.1. Overview</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="hash-index.html" title="Chapter 72. Hash Indexes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="hash-index.html" title="Chapter 72. Hash Indexes">Up</a></td><th width="60%" align="center">Chapter 72. Hash Indexes</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="hash-implementation.html" title="72.2. Implementation">Next</a></td></tr></table><hr /></div><div class="sect1" id="HASH-INTRO"><div class="titlepage"><div><div><h2 class="title" style="clear: both">72.1. Overview <a href="#HASH-INTRO" class="id_link">#</a></h2></div></div></div><p>3  <span class="productname">PostgreSQL</span>4  includes an implementation of persistent on-disk hash indexes,5  which are fully crash recoverable. Any data type can be indexed by a6  hash index, including data types that do not have a well-defined linear7  ordering. Hash indexes store only the hash value of the data being8  indexed, thus there are no restrictions on the size of the data column9  being indexed.10 </p><p>11  Hash indexes support only single-column indexes and do not allow12  uniqueness checking.13 </p><p>14  Hash indexes support only the <code class="literal">=</code> operator,15  so WHERE clauses that specify range operations will not be able to take16  advantage of hash indexes.17 </p><p>18  Each hash index tuple stores just the 4-byte hash value, not the actual19  column value. As a result, hash indexes may be much smaller than B-trees20  when indexing longer data items such as UUIDs, URLs, etc. The absence of21  the column value also makes all hash index scans lossy. Hash indexes may22  take part in bitmap index scans and backward scans.23 </p><p>24  Hash indexes are best optimized for SELECT and UPDATE-heavy workloads25  that use equality scans on larger tables. In a B-tree index, searches must26  descend through the tree until the leaf page is found. In tables with27  millions of rows, this descent can increase access time to data. The28  equivalent of a leaf page in a hash index is referred to as a bucket page. In29  contrast, a hash index allows accessing the bucket pages directly,30  thereby potentially reducing index access time in larger tables. This31  reduction in "logical I/O" becomes even more pronounced on indexes/data32  larger than shared_buffers/RAM.33 </p><p>34  Hash indexes have been designed to cope with uneven distributions of35  hash values. Direct access to the bucket pages works well if the hash36  values are evenly distributed. When inserts mean that the bucket page37  becomes full, additional overflow pages are chained to that specific38  bucket page, locally expanding the storage for index tuples that match39  that hash value. When scanning a hash bucket during queries, we need to40  scan through all of the overflow pages. Thus an unbalanced hash index41  might actually be worse than a B-tree in terms of number of block42  accesses required, for some data.43 </p><p>44  As a result of the overflow cases, we can say that hash indexes are45  most suitable for unique, nearly unique data or data with a low number46  of rows per hash bucket.47  One possible way to avoid problems is to exclude highly non-unique48  values from the index using a partial index condition, but this may49  not be suitable in many cases.50 </p><p>51  Like B-Trees, hash indexes perform simple index tuple deletion. This52  is a deferred maintenance operation that deletes index tuples that are53  known to be safe to delete (those whose item identifier's LP_DEAD bit54  is already set). If an insert finds no space is available on a page we55  try to avoid creating a new overflow page by attempting to remove dead56  index tuples. Removal cannot occur if the page is pinned at that time.57  Deletion of dead index pointers also occurs during VACUUM.58 </p><p>59  If it can, VACUUM will also try to squeeze the index tuples onto as60  few overflow pages as possible, minimizing the overflow chain. If an61  overflow page becomes empty, overflow pages can be recycled for reuse62  in other buckets, though we never return them to the operating system.63  There is currently no provision to shrink a hash index, other than by64  rebuilding it with REINDEX.65  There is no provision for reducing the number of buckets, either.66 </p><p>67  Hash indexes may expand the number of bucket pages as the number of68  rows indexed grows. The hash key-to-bucket-number mapping is chosen so that69  the index can be incrementally expanded. When a new bucket is to be added to70  the index, exactly one existing bucket will need to be "split", with some of71  its tuples being transferred to the new bucket according to the updated72  key-to-bucket-number mapping.73 </p><p>74  The expansion occurs in the foreground, which could increase execution75  time for user inserts. Thus, hash indexes may not be suitable for tables76  with rapidly increasing number of rows.77 </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="hash-index.html" title="Chapter 72. Hash Indexes">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="hash-index.html" title="Chapter 72. Hash Indexes">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="hash-implementation.html" title="72.2. Implementation">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 72. Hash Indexes </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 72.2. Implementation</td></tr></table></div></body></html>