codekingpro/portable-devtools
114k
1
2
3<!DOCTYPE html>
4
5<html lang="en" data-content_root="./">
6 <head>
7 <meta charset="utf-8" />
8 <meta name="viewport" content="width=device-width, initial-scale=1.0" /><meta name="viewport" content="width=device-width, initial-scale=1" />
9
10 <title>Index Dialog — pgAdmin 4 8.6 documentation</title>
11 <link rel="stylesheet" type="text/css" href="_static/pygments.css?v=fa44fd50" />
12 <link rel="stylesheet" type="text/css" href="_static/style.css?v=d36593c3" />
13
14 <script src="_static/documentation_options.js?v=d4c83366"></script>
15 <script src="_static/doctools.js?v=9a2dae69"></script>
16 <script src="_static/sphinx_highlight.js?v=dc90522c"></script>
17
18 <script src="_static/sidebar.js"></script>
19
20 <link rel="index" title="Index" href="genindex.html" />
21 <link rel="search" title="Search" href="search.html" />
22 <link rel="next" title="Primary key Dialog" href="primary_key_dialog.html" />
23 <link rel="prev" title="Foreign key Dialog" href="foreign_key_dialog.html" />
24 </head><body>
25 <div class="related" role="navigation" aria-label="related navigation">
26 <h3>Navigation</h3>
27 <ul>
28 <li class="right" style="margin-right: 10px">
29 <a href="genindex.html" title="General Index"
30 accesskey="I">index</a></li>
31 <li class="right" >
32 <a href="primary_key_dialog.html" title="Primary key Dialog"
33 accesskey="N">next</a> |</li>
34 <li class="right" >
35 <a href="foreign_key_dialog.html" title="Foreign key Dialog"
36 accesskey="P">previous</a> |</li>
37 <li class="nav-item nav-item-0"><a href="index.html">pgAdmin 4 8.6 documentation</a> »</li>
38 <li class="nav-item nav-item-1"><a href="modifying_tables.html" accesskey="U">Creating or Modifying a Table</a> »</li>
39 <li class="nav-item nav-item-this"><a href="">Index Dialog</a></li>
40 </ul>
41 </div>
42
43 <div class="document">
44 <div class="documentwrapper">
45 <div class="bodywrapper">
46 <div class="body" role="main">
47
48 <section id="index-dialog">
49<span id="id1"></span><h1><span class="target" id="index-0"></span>Index Dialog<a class="headerlink" href="#index-dialog" title="Link to this heading">¶</a></h1>
50<p>Use the <em>Index</em> dialog to create an index on a specified table or materialized
51view.</p>
52<p>The <em>Index</em> dialog organizes the development of a index through the following
53dialog tabs: <em>General</em>, <em>Definition</em>, and <em>Columns</em>. The <em>SQL</em> tab displays the SQL code
54generated by dialog selections.</p>
55<img alt="Index dialog general tab" class="align-center" src="_images/index_general.png" />
56<p>Use the fields in the <em>General</em> tab to identify the index:</p>
57<ul class="simple">
58<li><p>Use the <em>Name</em> field to add a descriptive name for the index. The name will
59be displayed in the <em>pgAdmin</em> tree control.</p></li>
60<li><p>Use the drop-down listbox next to <em>Tablespace</em> to select the tablespace in
61which the index will reside.</p></li>
62<li><p>Store notes about the index in the <em>Comment</em> field.</p></li>
63</ul>
64<p>Click the <em>Definition</em> tab to continue.</p>
65<img alt="Index dialog definition tab" class="align-center" src="_images/index_definition.png" />
66<p>Use the fields in the <em>Definition</em> tab to define the index:</p>
67<ul>
68<li><p>Use the drop-down listbox next to <em>Access Method</em> to select an index type:</p>
69<blockquote>
70<div><ul class="simple">
71<li><p>Select <em>btree</em> to create a B-tree index. A B-tree index may improve
72performance when managing equality and range queries on data that can be
73sorted into some ordering (the default).</p></li>
74<li><p>Select <em>hash</em> to create a hash index. A hash index may improve
75performance when managing simple equality comparisons.</p></li>
76<li><p>Select <em>gist</em> to create a GiST index. A GiST index may improve
77performance when managing two-dimensional geometric data types and
78nearest-neighbor searches.</p></li>
79<li><p>Select <em>gin</em> to create a GIN index. A GIN index may improve
80performance when managing values with more than one key.</p></li>
81<li><p>Select <em>spgist</em> to create a space-partitioned GiST index. A SP-GiST index
82may improve performance when managing non-balanced data structures.</p></li>
83<li><p>Select <em>brin</em> to create a BRIN index. A BRIN index may improve
84performance when managing minimum and maximum values and ranges.</p></li>
85</ul>
86</div></blockquote>
87</li>
88<li><p>Use the <em>Fill Factor</em> field to specify a fill factor for the index. The fill
89factor specifies how full the selected method will try to fill each index
90page.</p></li>
91<li><p>Use the <em>Gin pending list limit</em> field to specify the maximum size of a GIN index’s pending list, which is used
92when fastupdate is enabled. This value is specified in kilobytes.</p></li>
93<li><p>Use the <em>Pages per range</em> field to specify the number of table blocks that make up one block range
94for each entry of a BRIN index.</p></li>
95<li><p>Select <em>Buffering</em> to specify whether the buffering build technique is used to build the index. The default is
96<em>Auto</em></p></li>
97<li><p>Move the switch next to <em>Deduplicate items?</em> towards the <em>right position</em> to control usage of the B-tree
98deduplication technique. The default is <em>Yes</em>. This option is available only on PostgreSQL 13 and above.</p></li>
99<li><p>Move the switch next to <em>Fast update?</em> towards the <em>right position</em> to control usage of the fast update technique.
100The default is <em>Yes</em>.</p></li>
101<li><p>Move the switch next to <em>Autosummarize</em> towards the <em>right position</em> to define whether a summarization run is
102queued for the previous page range whenever an insertion is detected on the next one. The default is <em>No</em></p></li>
103<li><p>Move the switch next to <em>Unique?</em> towards the <em>right position</em> to check for duplicate values
104in the table when the index is created and when data is added. The default is <em>No</em>.</p></li>
105<li><p>Move the switch next to <em>NULLs not distinct?</em> towards the <em>right position</em> to treat null values as not distinct.
106The default is*No*. This option is available only on PostgreSQL 15 and above.</p></li>
107<li><p>Move the <em>Clustered?</em> switch to the <em>Yes</em> position to instruct the server to
108cluster the table.</p></li>
109<li><p>Move the <em>Concurrent build?</em> switch to the <em>Yes</em> position to build the index
110without taking any locks that prevent concurrent inserts, updates, or deletes
111on the table.</p></li>
112<li><p>Use the <em>Constraint</em> field to provide a constraint expression; a constraint
113expression limits the entries in the index to those rows that satisfy the
114constraint.</p></li>
115</ul>
116<p>Click the <em>Columns</em> tab to continue.</p>
117<img alt="Index dialog columns tab" class="align-center" src="_images/index_columns.png" />
118<p>Use the fields in the <em>Columns</em> tab to specify which column(s) or expression(s)
119the index queries. Use the <em>Is expression ?</em> switch to enable
120expression sql input. Use the drop-down listbox next to <em>Column</em>
121to select a column. Once the <em>Column</em> is selected or the <em>Expression</em> is
122entered then click the <em>Add</em> icon (+) to provide details of the action on the
123column/expression:</p>
124<ul>
125<li><p>The <em>Col/Exp</em> field is populated with the selection made in the <em>Column</em>
126drop-down listbox or the <em>Expression</em> entered.</p></li>
127<li><p>If enabled, use the drop-down listbox to select an available <em>Operator class</em>
128to specify the type of action performed on the column.</p></li>
129<li><p>If enabled, use the drop-down listbox to select <em>Sort order</em>:</p>
130<blockquote>
131<div><ul class="simple">
132<li><p>Select <em>ASC</em> to specify an ascending sort order (the default);</p></li>
133<li><p>Select <em>DESC</em> to specify a descending sort order.</p></li>
134</ul>
135</div></blockquote>
136</li>
137<li><p>If enabled, use the drop-down listbox to select <em>Nulls</em>:</p>
138<blockquote>
139<div><ul class="simple">
140<li><p>Select <em>First</em> to specify nulls sort before non-nulls;</p></li>
141<li><p>Select <em>Last</em> to specify nulls sort after non-nulls (the default).</p></li>
142</ul>
143</div></blockquote>
144</li>
145<li><p>Use the drop-down listbox in the <em>Collation</em> field to select a collation to
146use for the index.</p></li>
147</ul>
148<p>Use <em>Include columns</em> field to specify columns for <em>INCLUDE</em> clause of the
149constraint. This option is available in Postgres 11 and later.</p>
150<p>Click the <em>SQL</em> tab to continue.</p>
151<p>Your entries in the <em>Index</em> dialog generate a SQL command (see an example
152below). Use the <em>SQL</em> tab for review; revisit or switch tabs to make any
153changes to the SQL command.</p>
154<section id="example">
155<h2>Example<a class="headerlink" href="#example" title="Link to this heading">¶</a></h2>
156<p>The following is an example of the sql command generated by user selections in
157the <em>Index</em> dialog:</p>
158<img alt="Index dialog sql tab" class="align-center" src="_images/index_sql.png" />
159<p>The example shown demonstrates creating an index named <em>index_sal</em> that indexes
160the values in the <em>job</em> column of the <em>jobhist</em> table.</p>
161<ul class="simple">
162<li><p>Click the <em>Info</em> button (i) to access online help.</p></li>
163<li><p>Click the <em>Save</em> button to save work.</p></li>
164<li><p>Click the <em>Close</em> button to exit without saving work.</p></li>
165<li><p>Click the <em>Reset</em> button to restore configuration parameters.</p></li>
166</ul>
167</section>
168</section>
169
170
171 <div class="clearer"></div>
172 </div>
173 </div>
174 </div>
175 <div class="sphinxsidebar" role="navigation" aria-label="main navigation">
176 <div class="sphinxsidebarwrapper">
177 <div>
178 <h3><a href="index.html">Table of Contents</a></h3>
179 <ul>
180<li><a class="reference internal" href="#">Index Dialog</a><ul>
181<li><a class="reference internal" href="#example">Example</a></li>
182</ul>
183</li>
184</ul>
185
186 </div>
187<h3><a href="index.html">Table of Contents</a></h3>
188<ul class="current">
189<li class="toctree-l1"><a class="reference internal" href="getting_started.html">Getting Started</a></li>
190<li class="toctree-l1"><a class="reference internal" href="external_database.html">External database for pgAdmin user settings</a></li>
191<li class="toctree-l1"><a class="reference internal" href="connecting.html">Connecting To A Server</a></li>
192<li class="toctree-l1"><a class="reference internal" href="managing_cluster_objects.html">Managing Cluster Objects</a></li>
193<li class="toctree-l1"><a class="reference internal" href="managing_database_objects.html">Managing Database Objects</a></li>
194<li class="toctree-l1 current"><a class="reference internal" href="modifying_tables.html">Creating or Modifying a Table</a><ul class="current">
195<li class="toctree-l2"><a class="reference internal" href="check_dialog.html">Check Dialog</a></li>
196<li class="toctree-l2"><a class="reference internal" href="column_dialog.html">Column Dialog</a></li>
197<li class="toctree-l2"><a class="reference internal" href="compound_trigger_dialog.html">Compound Trigger Dialog</a></li>
198<li class="toctree-l2"><a class="reference internal" href="exclusion_constraint_dialog.html">Exclusion Constraint Dialog</a></li>
199<li class="toctree-l2"><a class="reference internal" href="foreign_key_dialog.html">Foreign key Dialog</a></li>
200<li class="toctree-l2 current"><a class="current reference internal" href="#">Index Dialog</a></li>
201<li class="toctree-l2"><a class="reference internal" href="primary_key_dialog.html">Primary key Dialog</a></li>
202<li class="toctree-l2"><a class="reference internal" href="rls_policy_dialog.html">RLS Policy Dialog</a></li>
203<li class="toctree-l2"><a class="reference internal" href="rule_dialog.html">Rule Dialog</a></li>
204<li class="toctree-l2"><a class="reference internal" href="table_dialog.html">Table Dialog</a></li>
205<li class="toctree-l2"><a class="reference internal" href="trigger_dialog.html">Trigger Dialog</a></li>
206<li class="toctree-l2"><a class="reference internal" href="unique_constraint_dialog.html">Unique Constraint Dialog</a></li>
207</ul>
208</li>
209<li class="toctree-l1"><a class="reference internal" href="management_basics.html">Management Basics</a></li>
210<li class="toctree-l1"><a class="reference internal" href="backup_and_restore.html">Backup and Restore</a></li>
211<li class="toctree-l1"><a class="reference internal" href="developer_tools.html">Developer Tools</a></li>
212<li class="toctree-l1"><a class="reference internal" href="processes.html">Processes</a></li>
213<li class="toctree-l1"><a class="reference internal" href="pgagent.html">pgAgent</a></li>
214<li class="toctree-l1"><a class="reference internal" href="contributions.html">pgAdmin Project Contributions</a></li>
215<li class="toctree-l1"><a class="reference internal" href="release_notes.html">Release Notes</a></li>
216<li class="toctree-l1"><a class="reference internal" href="licence.html">Licence</a></li>
217</ul>
218
219<search id="searchbox" style="display: none" role="search">
220 <h3 id="searchlabel">Quick search</h3>
221 <div class="searchformwrapper">
222 <form class="search" action="search.html" method="get">
223 <input type="text" name="q" aria-labelledby="searchlabel" autocomplete="off" autocorrect="off" autocapitalize="off" spellcheck="false"/>
224 <input type="submit" value="Go" />
225 </form>
226 </div>
227</search>
228<script>document.getElementById('searchbox').style.display = "block"</script>
229 </div>
230<div id="sidebarbutton" title="Collapse sidebar">
231<span>«</span>
232</div>
233
234 </div>
235 <div class="clearer"></div>
236 </div>
237 <div class="related" role="navigation" aria-label="related navigation">
238 <h3>Navigation</h3>
239 <ul>
240 <li class="right" style="margin-right: 10px">
241 <a href="genindex.html" title="General Index"
242 >index</a></li>
243 <li class="right" >
244 <a href="primary_key_dialog.html" title="Primary key Dialog"
245 >next</a> |</li>
246 <li class="right" >
247 <a href="foreign_key_dialog.html" title="Foreign key Dialog"
248 >previous</a> |</li>
249 <li class="nav-item nav-item-0"><a href="index.html">pgAdmin 4 8.6 documentation</a> »</li>
250 <li class="nav-item nav-item-1"><a href="modifying_tables.html" >Creating or Modifying a Table</a> »</li>
251 <li class="nav-item nav-item-this"><a href="">Index Dialog</a></li>
252 </ul>
253 </div>
254 <div class="footer" role="contentinfo">
255 <div class="related" role="navigation" aria-label="related navigation">
256 <ul>
257 <li class="left" style="margin-left: 10px">© Copyright (C) 2013 - 2024, The pgAdmin Development Team.</li>
258 <li class="right" style="margin-right: 10px"><a href="genindex.html" title="General Index" accesskey="I">index</a></li>
259 <li class="right" ><a href="primary_key_dialog.html" title="Primary key Dialog" accesskey="N">next</a> |</li>
260 <li class="right" ><a href="foreign_key_dialog.html" title="Foreign key Dialog" accesskey="P">previous</a> |</li>
261 </ul>
262 </div>
263 </div>
264 </body>
265</html>