Create an Index and Check Whether the Plan Changes

Goal

You create several kinds of indexes yourself and see with your own eyes how the execution plan changes. The purpose of this lab is to know when an index is used and when it is ignored, rather than how to create one.

Why it matters

When you create an index and the plan stays the same, the most common advice is to force it. This is not a diagnosis but a suppression of the symptom, and in fact there are many cases where forcing it makes things slower. An index scan visits heap pages at random for each matched row, so if there are many rows to return, it ends up reading the entire table in random order.

So the questions to ask when an index is not used split into two. Is it not used because the optimizer is right, or can it not be used because my query has a form that cannot use the index? In the former case you must change the index or give up on it, and in the latter you must fix the query. The responses are opposite, so distinguishing comes first.

In this lab you will also create partial indexes, expression indexes, and covering indexes, getting a feel that "an index is not one thing but several kinds".

Steps

1. Save the output of EXPLAIN SELECT count(*) FROM orders WHERE channel = 'partner'; not to /root/exp but to /root/plan_seq.txt. A sequential scan must appear.
2. On orders (ordered_at), create a B-tree index with the name idx_orders_ordered_at.
3. Save the EXPLAIN output of a query with a range condition on ordered_at to /root/plan_idx.txt. idx_orders_ordered_at must appear in the plan.
4. On order_items, create a composite index named idx_order_items_order_product on (order_id, product_id), in that order.
5. On orders (ordered_at), create a partial index with the condition status = 'pending' attached, named idx_orders_pending.
6. On customers, create an expression index on lower(email) named idx_customers_email_lower.
7. On products, create a covering index whose key columns are (category, price) and whose INCLUDE column is name, named idx_products_cat_price.
8. With sequential scans turned off, take a query that uses a condition on ordered_at to compute count(*), and save its EXPLAIN output to /root/plan_only.txt. Index Only Scan must appear.

Notes

• Check index definitions: \d orders or SELECT indexdef FROM pg_indexes WHERE tablename='orders';
• Compare index sizes: SELECT pg_size_pretty(pg_relation_size('인덱스이름')); (put the index name in place of the Korean word)
• Turn off sequential scans temporarily: SET enable_seqscan = off; — use it only for diagnosis. You must not leave it as a production setting.
• Common mistake 1: if you give an index a different name, it will not be graded. Use the names in the instructions as they are.
• Common mistake 2: an expression index is used only when the query uses exactly the same expression. An index on lower(email) is not used for a lower(trim(email)) condition.

Save the plan when there is no index

Save the output of EXPLAIN SELECT count(*) FROM orders WHERE channel = 'partner'; not to /root/exp but to /root/plan_seq.txt. A sequential scan must appear.

You only need to put EXPLAIN in front of the query. A sequential scan appears only if you use as the condition a column that has no index yet.

Create a basic B-tree index

On orders (ordered_at), create a B-tree index with the name idx_orders_ordered_at.

The form is CREATE INDEX name ON table (column). You must match the index name exactly to be graded.

Save a plan that uses the index

Save the EXPLAIN output of a query with a range condition on ordered_at to /root/plan_idx.txt. idx_orders_ordered_at must appear in the plan.

You must apply a condition with a narrow range for the optimizer to choose the index. If you wrap the column in a function, you cannot use the index.

Decide the column order of a composite index

On order_items, create a composite index named idx_order_items_order_product on (order_id, product_id), in that order.

The column often used with an equality condition must come first. If the order changes, it is a different index.

Reduce size with a partial index

On orders (ordered_at), create a partial index with the condition status = 'pending' attached, named idx_orders_pending.

With the form CREATE INDEX ... WHERE condition, you can hold only some rows. Compare the sizes.

Create an expression index

On customers, create an expression index on lower(email) named idx_customers_email_lower.

You can create an index on the result of a computation rather than on a column. You must wrap it in parentheses, and the query must use exactly the same expression for the index to be used.

Reduce heap visits with a covering index

On products, create a covering index whose key columns are (category, price) and whose INCLUDE column is name, named idx_products_cat_price.

There is a clause that lets you define separately the key columns used for searching and the columns needed only for the result.

Observe an Index Only Scan

With sequential scans turned off, take a query that uses a condition on ordered_at to compute count(*), and save its EXPLAIN output to /root/plan_only.txt. Index Only Scan must appear.

If the table is small, the optimizer chooses a sequential scan. For diagnostic purposes, block sequential scans for a moment and get the plan again.