Which indexes to create

Short rules of thumb. Some of these are created automatically, but can possibly be manually dropped later, depending on your dbms.

Don't assume you will always work on PostgreSQL. Every primary key means that multi-column primary keys should have a single index covering all columns. PostgreSQL will create this index automatically if you declare a multi-column primary key.

There are many cases in which a single multi-column index gives you better performance than several single-column indexes. Monitor slow queries and do testing to figure out which is which.

Assume that any change to indexing will improve some database activities and degrade others. I find it helpful to have a set of SQL statements that I can profile before and after making changes to indexes.

In addition to what Catcall already provided , and to add a small corrective:. I also covered some basics in this closely related answer on SO recently. Answers so far seem to indicate you need to create indexes on primary keys, but that's not the case in PostgreSQL partial exceptions apply. I quote the manual here :.

PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint a multicolumn index, if appropriate , and is the mechanism that enforces the constraint.

You may want to create additional indexes for the second or later columns of a multi-column index, but the first one is generally covered just fine by a multicolumn index - except when additional columns make the index much larger. We discussed that in great detail under this related question:. Is a composite index also good for queries on the first field?

Multicolumn indexes , partial indexes and indexes on expressions are particularly powerful tools in PostgreSQL. Since PostgreSQL 9. Every index carries specific costs , so there is no way around some basic knowledge to really optimize indexing. Just creating more indexes can do more harm than good. In particular, indexes can prevent HOT updates from improving performance.

Too many indexes can exhaust the cache memory so that even read operations can suffer. Finally, this Postgres Wiki page on index maintenance features tools to find duplicate or unused indexes among other things. The answer for doing it yourself is pretty exhaustively documented here. So let's look at something else. Pghero may be able to assist you if you want some automated advice. Check out this video for more information. Sign up to join this community. Click Add to add current entry and leave the dialog box open for additional entries.

Click Add All to locate all instances of the selected text in the document window and create an index marker for each one. Add All is available only if text in the document is selected. If you click Cancel after clicking Add, the entries you just added are not removed. Use the Undo command to remove these entries.

To close the dialog box, click OK or Done. Using an indexing shortcut, you can quickly index individual words, a phrase, or a list of words or phrases. Adobe InDesign recognizes two indexing shortcuts: one for standard index entries; the other for proper names. The proper name shortcut creates index entries by reversing the order of a name so it is alphabetized by the last name.

In this way, you can list a name with the first name first, but have it appear in the index sorted by last name. The indexing shortcut adds a marker in front of each entry and places all the items into the index.

An index marker using the default settings is added at the beginning of the selection or to the beginning of each item selected. To index compound last names or names with a title, include one or more nonbreaking spaces between the words. When you select terms like kanji for which a yomi is required and set the index marker using a shortcut, the New Page Reference dialog box will open automatically if the yomi has not been input, and the term selected will be displayed in the Topic Levels dialog box.

Input the yomi corresponding to the text input in the Topic Levels box in the Yomi text box. When the same index entry appears on several pages, the yomi for all the index entries can be changed in a single step. To change only the index entry selected, select the page number in the Index panel and Page Reference Options from the Index panel menu.

When this happens, you can create multiple index entries based on other entries already in your index to ensure consistency. In the Index panel, select Reference , and scroll the preview area to the entry you want to copy. Drag an entry to the New Entry button to insert an index marker at the insertion point, or at the beginning of the selection. The New Page Reference dialog box appears, with information about the selected entry.

Make any changes, and then click Add or OK. Alternatively, you can create a topic list, and then select topics from the list as you create index entries. Using the Add All option is an effective way to index all occurrences of a specified term in a document or a book.

When you click Add All, InDesign creates index markers at every occurrence of the words selected in the document—not the text specified in the dialog box. You can later delete entries that point to less significant information. When searching for occurrences of the selected text, InDesign considers only whole words, and searches are case-sensitive. For example, if cheetah is selected, cheetahs and Cheetah will not be indexed. To create index entries for any open documents in the book, select Book.

Choose Add All. InDesign adds index markers to all text that matches the selected text, regardless of whether the text has been indexed, so you may end up with multiple entries for the same word or phrase. To change a topic such as renaming it or applying a new sort order and automatically update all entries that use that topic, use the Index panel in Topic mode.

To change one entry at a time, use Reference mode. Select Topic to edit a topic and automatically update all entries using that topic. You can create index entries that include a page range such as cats 82—87 instead of a single page number.

Current Page. To Next Style Change. To Next Use Of Style. To End Of Story. The page range extends from the index marker to the end of the current thread of text frames that contain text.

To End Of Document. To End Of Section. The page range extends from the index marker to the end of the current section as defined in the Pages panel. See Document numbering options. For Next Of Paragraphs.

The page range extends from the index marker to the end of the number of paragraphs specified in the adjacent box, or to the end of as many paragraphs as exist. For Next Of Pages. The page range extends from the index marker to the end of the number of pages specified in the adjacent box, or to the end of as many pages as exist.

Suppress Page Range. Cross-references are index entries that point to related entries, instead of a page number. You create cross-references using the Index panel. Cross-references can serve different purposes in an index:. Cross-references associate common terms with equivalents used in your document or book.

For example, Fauna. See Animals. Entries with such cross-references do not contain page references; they simply point to equivalent terms that are indexed more fully. Cross-references point to other entries related to, but not equivalent to, a topic. For example, Cats. See also Wildcats. Cross-reference to related information See also B. Cross-reference to an equivalent term See. When you create a cross-reference in InDesign, you can also select a cross-reference prefix.

In the Type menu, choose a cross-reference prefix such as See also from the bottom of the menu. Type a topic in the Referenced box, or drag an existing topic from the topic list at the bottom. Cross-references appear in the Index panel and the generated index, but are not associated with index markers in the document itself.

An index story can appear as a separate document or in an existing document. When you generate an index story, InDesign compiles index entries and updates page numbers across your document or book. If an index marker appears in overset text when you generate the index, you are asked if you would like to include these markers in the index.

If you click Yes, the entry appears in the index without a page number. If an index marker appears in hidden conditional text, the entry is excluded from the index. For Title, type the text that will appear at the top of the index. Select Replace Existing Index to update an existing index. Deselecting this options lets you create multiple indexes. Deselect this option if you want to generate an index for the current document only.

Select Include Entries On Hidden Layers if you want index markers on hidden layers to be included in your index. If you edit entries in the index story, these changes are overridden when you regenerate the index. For best results, edit the index in the Index panel, and then generate the index again. When you click More Options in the Generate Index dialog box, formatting options appear that let you determine the style and appearance of the generated index.

After you generate the index, you can edit these styles in the Paragraph Styles and Character Styles panels. Level 1 entry D. Level 2 subentry E. To replace the entry separators such as the values for Following Topic or Between Entries , select the existing separator and then type or choose a replacement character.

Nested or Run-in. Select Nested if you want the index formatted in the default style, with subentries nested under an entry as separate indented paragraphs. Select Run-in if you want all levels of an entry to appear in a single paragraph. The Between Entries option determines which character separates the entries. Include Index Section Headings. Select this option to generate section headings consisting of alphabet characters A, B, C, and so on representing the section that follows.

Include Empty Index Sections. Select this option to generate section headings for all letters of the alphabet, even if the index lacks any first-level entries that begin with a particular letter. For each index level, choose a paragraph style to be applied to each level of index entries. You can edit these styles in the Paragraph Styles panel after you generate the index.

Section Heading. Select the paragraph style that determines the appearance of the section headings A, B, C, and so on in the generated index. Select the character style that determines the appearance of the page numbers in the generated index.

This setting does not affect index entries you formatted using the Number Style Override option. Cross-referenced Topic. Select the character style that determines the appearance of the topic being referred to such as beasts in See also beasts in the generated index.

Following Topic. Type or select a special character to separate the entry from the page number such as Animals The default is two spaces. Determine formatting for this character by editing the corresponding Level Style, or by selecting another. Between Page Numbers.

Type or select a special character to separate one page number or range from another. The default is a comma followed by an en space. Between Entries. If Run-in is selected, type or select a special character to determine how entries and subentries are separated. If Nested is selected, this setting determines how two cross-references under a single entry are to be separated. Before Cross-reference.

Type or select a special character that appears between a reference and a cross-reference, as in Animals. See also beasts. The default is a period followed by a space. Determine formatting for this character by switching or editing the corresponding level style. Type or select a special character to separate the first and last numbers in a page range such as Animals 38— The default is an en dash.

Determine formatting for this character by switching or editing the Page Number style. Type or select a special character to appear at the end of entries. If Run-in is selected, the specified character appears at the end of the last cross-reference. To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:. Because each query can only use one index, single-column indexes are not typically as useful as multiple-column indexes.

To create the most useful multiple-column indexes, we recommend reviewing our best practices. The above example is equivalent to:. Spatial indexes are a special type of inverted index. Only the syntax shown here is supported. Most users should not change the default spatial index settings. There is a risk that you will get worse performance by changing the default settings. For more information , see Spatial indexes.

Storing a column improves the performance of queries that retrieve but do not filter its values. However, to use stored columns, queries must filter another column in the same index. To sort columns in descending order, you must explicitly set the option when creating the index. Ascending order is the default. Note that how a column is ordered in the index will affect the ordering of the index keys, and may affect the efficiency of queries that include an ORDER BY clause.

Normally, CockroachDB selects the index that it calculates will scan the fewest rows. However, you can override that selection and specify the name of the index you want to use. For performance reasons, we discourage indexing on sequential keys. If, however, you are working with a table that must be indexed on sequential keys, you should use hash-sharded indexes.

Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range hotspots and improving write performance on sequentially-keyed indexes at a small cost to read performance. Why CockroachDB.