Database Essentials using Postgres - Creating Tables and Indexes - Indexes on Tables

Databases Postgres
1

Description:
This article discusses the importance of indexes in RDBMS, focusing on unique and non-unique indexes, B Tree indexes, and criteria for creating indexes. It also provides hands-on tasks for dropping and recreating tables, loading data, creating an index, and measuring query performance.

Explanation for the Video:

[Placeholder for video embed]

Key Concepts Explanation

Unique Index

A unique index enforces uniqueness and sorts data in ascending order. Code Example:

CREATE UNIQUE INDEX idx_name ON table_name(column_name);

Non Unique Index

A non-unique index sorts data in ascending order but does not enforce uniqueness. Code Example:

CREATE INDEX idx_name ON table_name(column_name);

B Tree Index

B Tree indexes are the most commonly used indexes for sparsely populated columns. Code Example:

CREATE INDEX idx_name ON table_name(column_name) USING BTREE;

Hands-On Tasks

  1. Drop and recreate retail db tables.
  2. Load data into retail db tables.
  3. Compute statistics (Optional).
  4. Use code to randomly fetch 2000 orders and join with order_items - compute time.
  5. Create index for order_items.order_item_order_id and compute statistics.
  6. Use code to randomly fetch 2000 orders and join with order_items - compute time.

Conclusion

In conclusion, indexes play a crucial role in optimizing query performance in RDBMS. By following the guidelines for creating indexes and performing hands-on tasks, you can improve the efficiency of your database operations. Engage with the community and continue practicing to enhance your understanding of indexes in RDBMS.

Indexes on Tables

  • An index can be unique or non-unique.
  • Unique Index: Data is sorted in ascending order and uniqueness is enforced.
  • Non-Unique Index: Data is sorted in ascending order and uniqueness is not enforced.
  • Unless specified, all indexes are of type B Tree.
  • B Tree indexes are commonly used for sparsely populated columns.
  • Bitmap indexes are suitable for densely populated columns with very few distinct values.
  • Write operations may become slower with more indexes added to a table.
  • Guidelines for creating indexes:
    • Create unique indexes to enforce uniqueness.
    • Index foreign key columns used for joins.

Let’s start with the hands-on tasks related to indexes.

Watch the video tutorial here