Query Performance Difference Between Two Multicolumn Indexes - Intermediate Book Store Indexes Project

Hey everyone,

I’m currently working on the Intermediate Book Store Indexes exercises on Codecademy, specifically focusing on building multicolumn indexes for better query performance.

In exercises 8 and 9, the task involves experimenting with multicolumn indexes on the orders table, specifically targeting customer_id, book_id, and quantity.

However, I encountered an unexpected performance difference between two multicolumn indexes when running a specific query:

sqlCopy code

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 25921 and book_id = 133 and quantity > 10;

Here are the observed results:

Index 1:

  • Index: orders_customer_id_book_id_idx
  • Execution Time: 0.020 ms
  • Query Plan:

csharpCopy code

Bitmap Heap Scan on orders...
Bitmap Index Scan on orders_customer_id_book_id_idx...

Index 2:

  • Index: orders_customer_id_book_id_quantity_idx
  • Execution Time: 0.031 ms
  • Query Plan:

csharpCopy code

Index Scan using orders_customer_id_book_id_quantity_idx on orders...

query performance

Surprisingly, the index involving three columns (customer_id, book_id, and quantity) is performing slower than the index on two columns (customer_id and book_id).

I expected the three-column index to be more optimized for this query since it includes all the filtering criteria. However, the performance difference seems counterintuitive.

Could someone help me understand why the three-column index isn’t performing as well as the two-column index for this particular query? Any insights or suggestions on how to optimize the index or query further would be greatly appreciated!

  • I’d like to highlight that I intentionally decided not to drop the other index (orders_customer_id_book_id_idx ) to maintain it for comparison purposes. This way, I can compare the performance between the two indexes.

Additionally, if you need more specific details or any other information, please let me know.

Thank you so much for your time and assistance!