Performance Tuning using Indexes
Let us understand how we can improve the performance of the query by creating index on order_items.order_item_order_id.
-
We have order level details in orders and item level details in order_items.
-
When customer want to review their orders, they need details about order_items. In almost all the scenarios in order management system, we prefer to get both order as well as order_items details by passing order_id of pending or outstanding orders.
-
Let us review the explain plan for the query with out index on order_items.order_item_order_id.
EXPLAIN
SELECT o.*,
oi.order_item_subtotal
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_id = 2;
-
Develop piece of code to randomly pass 2000 order ids and calculate time.
!pip install psycopg2
import psycopg2
%%time
connection = psycopg2.connect(
host='localhost',
port='5432',
database='itversity_retail_db',
user='itversity_retail_user',
password='retail_password'
)
cursor = connection.cursor()
query = '''SELECT count(1)
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_id = %s
'''
ctr = 0
while True:
if ctr == 2000:
break
cursor.execute(query, (1,))
ctr += 1
cursor.close()
connection.close()
-
Create index on order_items.order_item_order_id
%load_ext sql
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db
%%sql
CREATE INDEX order_items_order_id_idx
ON order_items(order_item_order_id);
-
Run explain plan after creating index on order_items.order_item_order_id
EXPLAIN
SELECT o.*,
oi.order_item_subtotal
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_id = 2;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.71..16.81 rows=3 width=34)
-> Index Scan using orders_pkey on orders o (cost=0.29..8.31 rows=1 width=26)
Index Cond: (order_id = 2)
-> Index Scan using order_items_order_id_idx on order_items oi (cost=0.42..8.47 rows=3 width=12)
Index Cond: (order_item_order_id = 2)
(5 rows)
-
Run the code again to see how much time, it get the results for 2000 random orders.
import psycopg2
%%time
from random import randrange
connection = psycopg2.connect(
host='localhost',
port='5432',
database='itversity_retail_db',
user='itversity_retail_user',
password='retail_password'
)
cursor = connection.cursor()
query = '''SELECT count(1)
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_id = %s
'''
ctr = 0
while True:
if ctr == 2000:
break
order_id = randrange(1, 68883)
cursor.execute(query, (order_id,))
ctr += 1
cursor.close()
connection.close()