Database Query Optimisation Report

Analysis of Django ORM Logs

1. Introduction

This report analyses the actual SQL statements captured in the Django ORM log for a library management system. The aim is not only to list generic optimisation techniques, but to define the use cases, identify the real queries executed during those use cases, explain how the application interacts with the database, and then propose optimised query patterns.

2. UML / Data Model View

The core data model contains users, books, and borrow transactions. A borrow record links one library user with one book, while one user and one book can appear in multiple borrow records over time.

3. Use Case 1: Borrowing a Book

3.1 Use Case Description

This use case occurs when an administrator or user creates a borrowing record. The Django ORM log shows the application selecting the related user and book, checking existence, inserting the borrow record, inserting an admin log record, and committing the transaction.

3.2 Queries Before Optimisation

The ORM log shows multiple database calls before the actual insert:

SELECT "library_user"."id", "library_user"."first_name", "library_user"."last_name",
      "library_user"."email", "library_user"."address", "library_user"."phone", "library_user"."gender"
FROM "library_user"
WHERE "library_user"."id" = 3
LIMIT 21;

SELECT "library_book"."id", "library_book"."title", "library_book"."author",
      "library_book"."isbn", "library_book"."category", "library_book"."available_copies"
FROM "library_book"
WHERE "library_book"."id" = 2
LIMIT 21;

SELECT 1 AS "a" FROM "library_user" WHERE "library_user"."id" = 3 LIMIT 1;
SELECT 1 AS "a" FROM "library_book" WHERE "library_book"."id" = 2 LIMIT 1;

INSERT INTO "library_borrow" ("user_id", "book_id", "borrow_date", "return_date", "returned")
VALUES (3, 2, '2026-03-23'::date, '2025-09-29'::date, false)
RETURNING "library_borrow"."id";

3.3 Interaction Problem

• The ORM retrieves full user and book records even though only their IDs are required to create the borrow record.
• The ORM then performs additional existence checks using SELECT 1 queries for the same user_id and book_id.
• This creates redundant database round trips before the INSERT.
• The borrow insert itself is correct, but the pre-check workflow can be made more efficient.

3.4 Optimised Query Pattern

A more efficient SQL pattern checks user and book existence in the insert itself and returns the created borrow ID only when both records exist:

INSERT INTO library_borrow (user_id, book_id, borrow_date, return_date, returned)
SELECT u.id, b.id, CURRENT_DATE, DATE '2026-04-06', false
FROM library_user u
JOIN library_book b ON b.id = 2
WHERE u.id = 3
RETURNING id;

In Django, this can also be improved by assigning foreign key IDs directly when the application already has validated IDs:

Borrow.objects.create(
   user_id=3,
   book_id=2,
   borrow_date=date.today(),
   return_date=return_date,
   returned=False
)

3.5 Optimisation Analytics

4. Use Case 2: Viewing Borrow Records

4.1 Query Before Optimisation

The original query retrieves a large number of columns from multiple related tables and also performs repeated COUNT operations. This increases database workload and may slow down system performance, especially when the borrow table contains a large amount of data. Fetching unnecessary columns also increases memory usage and network transfer between the database and application.The borrow list page triggers repeated count queries and a join that retrieves many columns from all three related tables:

SELECT COUNT(*) AS "__count" FROM "library_borrow";
SELECT COUNT(*) AS "__count" FROM "library_borrow";

SELECT "library_borrow"."id", "library_borrow"."user_id", "library_borrow"."book_id",
      "library_borrow"."borrow_date", "library_borrow"."return_date", "library_borrow"."returned",
      "library_user"."id", "library_user"."first_name", "library_user"."last_name",
      "library_user"."email", "library_user"."address", "library_user"."phone", "library_user"."gender",
      "library_book"."id", "library_book"."title", "library_book"."author",
      "library_book"."isbn", "library_book"."category", "library_book"."available_copies"
FROM "library_borrow"
INNER JOIN "library_user" ON ("library_borrow"."user_id" = "library_user"."id")
INNER JOIN "library_book" ON ("library_borrow"."book_id" = "library_book"."id")
ORDER BY "library_borrow"."id" DESC;

4.2 Optimised Query

The optimised query improves performance by selecting only the columns required for displaying borrow records. Pagination using LIMIT and OFFSET is also applied to prevent loading the entire table at once. This reduces query execution time, lowers memory consumption, and provides a more efficient way of displaying records in the application. The optimised query fetches only columns needed for the list view and applies pagination:

SELECT lb.id,
      u.first_name,
      u.last_name,
      b.title,
      lb.borrow_date,
      lb.return_date,
      lb.returned
FROM library_borrow lb
JOIN library_user u ON lb.user_id = u.id
JOIN library_book b ON lb.book_id = b.id
ORDER BY lb.id DESC
LIMIT 20 OFFSET 0;

Recommended Django ORM equivalent:

Borrow.objects.select_related('user', 'book')    .only('id', 'borrow_date', 'return_date', 'returned',
         'user__first_name', 'user__last_name', 'book__title')    .order_by('-id')[:20]

4.3 Optimisation Analytics

1) Duplicate COUNT queries should be avoided unless pagination specifically requires the total number of records. Reducing unnecessary count operations helps decrease database workload and improves query efficiency.

2) Fetching only the columns required for the borrow list view reduces memory usage and minimizes unnecessary network data transfer between the database and application.

3) Using LIMIT and OFFSET prevents the system from loading the entire borrow table at once, which improves scalability and response time for larger datasets.

4) The use of select_related is appropriate because Borrow contains foreign key relationships with User and Book. This helps avoid N+1 query problems and improves overall query performance.

5. Indexing Recommendations

Indexing is an important technique used to improve database performance and speed up data retrieval operations. Without indexes, the database system may need to scan the entire table to find specific records, which can increase query execution time, especially when dealing with large amounts of data.

For this project, it is recommended to create indexes on primary key and foreign key columns because these attributes are frequently used in searching and join operations. Indexing commonly searched fields such as IDs, usernames, or order numbers can also improve system efficiency and reduce response time.

Although indexes improve query performance, excessive indexing may slightly reduce insert and update performance because indexes also need to be updated whenever data changes occur. Therefore, indexes should be applied carefully to the most frequently accessed attributes to maintain a balance between performance and storage efficiency. Foreign key columns are commonly used in joins and filters. The following indexes support the observed borrowing and borrow-record viewing queries:

CREATE INDEX IF NOT EXISTS idx_library_borrow_user_id ON library_borrow(user_id);
CREATE INDEX IF NOT EXISTS idx_library_borrow_book_id ON library_borrow(book_id);
CREATE INDEX IF NOT EXISTS idx_library_borrow_id_desc ON library_borrow(id DESC);

Note: PostgreSQL automatically indexes primary keys, but indexing foreign key columns used in joins can still improve query performance as the dataset grows.

6. Summary of Before and After Optimisation

7. Conclusion

This report analysed the SQL queries generated by the Django ORM in the Library Management System. The main objective was to identify inefficient query patterns and propose optimisation techniques based on the actual ORM log entries collected during system execution.

The analysis showed that some operations generated unnecessary database activity, including repeated existence checks, retrieval of full table rows when only IDs were required, repeated COUNT queries, and joins that fetched excessive columns. These query patterns increase database workload, memory usage, and response time, especially when the dataset grows larger.

To improve performance, several optimisation techniques were proposed. For the borrowing process, direct foreign key assignment and combined validation queries were recommended to reduce redundant database calls. For viewing borrow records, selecting only required columns, applying pagination using LIMIT and OFFSET, and using Django’s select_related() method helped reduce unnecessary joins and improve query efficiency.

The report also highlighted the importance of indexing foreign key columns used in joins and ordering operations. Proper indexing can improve query execution speed and scalability for frequently accessed tables such as library_borrow.

Overall, the optimisation strategies discussed in this report demonstrate how analysing real Django ORM queries can help improve database efficiency, reduce unnecessary processing, and create a more scalable Library Management System.

Appendix: Log Evidence Used

The report is based on the following observed log entries:

• ORM_log.log: repeated SELECT COUNT(*) on library_borrow before loading the borrow list.
• ORM_log.log: SELECT queries on library_user and library_book followed by INSERT INTO library_borrow.
• ORM_log.log: INSERT INTO django_admin_log and COMMIT after borrow creation.