Using and creating indexes
1. Using and creating indexes
Now let's look at another row-oriented storage optimization method, indexes.2. Index overview
An index creates sorted column keys to improve search. Consider a book index. In a book, you look for a subject and find all the pages related to that subject. In a database, the database looks for a pointer, the index, and finds all that pointer's data items. The index is a reference to the data's location. Indexes are used to speed queries. Searching ordered keys instead of the raw data is faster. Indexes are often on a column used for filtering such as a date or location. Many databases create an index on a table's primary key, the column describing its uniqueness.3. Index example
Consider this simple cookbook table that lists recipes with their ingredients. People often query the table to find specific recipes, so the recipe column has an index.4. Index as a key and pointer
This index creates a key or pointer column. You can see the four values for fried rice sorted together in the index. When you query the cookbook table, the query looks at this ordered index table. The query quickly finds all the fried rice entries because they are together. The query uses the corresponding pointer values to find the fried rice rows in the original cookbook table. Searching a sorted, grouped index is faster than searching each row of the cookbook table.5. Finding existing indexes
Usually, a database administrator will set up and maintain indexes. However, anyone can find existing indexes with a query. Postgres has a pg_tables schema. Similar to the information schema, this schema contains views with metadata about the database.6. Finding existing indexes
Pg_indexes is a view that contains all the indexes. It lists the schema, table, index name, storage location, and the index creation SQL statement. Many databases create indexes on primary keys, so most tables will have at least one column with an index.7. Creating an index
Anyone can create an index. The syntax is simple. CREATE INDEX, the index name, ON, the table name, the column name. This example shows a recipe index created on the cookbook table. You can also create an index on multiple columns. This second query creates an index using both the recipe and serving_size columns. It adds the CONCURRENTLY function. CONCURRENTLY allows records to load into the cookbook table while the index is being created. This prevents the table from being locked as the index is being built.8. To use or not to use
Indexes are helpful when working with large tables, or any table that is slow to query. Columns that are common filter conditions are good candidates for indexes. Indexes are often used on primary keys. Indexes are not suitable for every scenario. On small tables, they offer little to no performance boost. Columns with many nulls are not suitable. Frequently updated tables cause indexes to become fragmented. Additionally, the data must be written twice, into the table and into the index.9. Frequently updated tables
For instance, a basil entry was added to the cookbook table after the index was created. The index is now fragmented. The basil entry is not with the other spaghetti and meatball records. Re-indexing will fix fragmentation, but it is an additional processing step.10. Index query assessment
Recall the query planner that uses your order (or SQL) to plan your meal execution (or query). Adding EXPLAIN before a query shows this execution plan. The units are not intuitive, and we will explore them in depth in chapter four. Right now, we will focus on the cost estimate, a relative measure of query time. The query cost will decrease when an index is added to a column.11. Let's practice!
Your turn to practice using indexes.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.