Vidhyadeep University Students: Reading Database Query Plans in Your Backend Internship

To read a query plan, run EXPLAIN (or EXPLAIN ANALYZE) on the slow query, then check three things: how the table is accessed (scan or index), how many rows the database expected versus actually touched, and where most of the time goes. Fix the most expensive node first, usually with an index or a rewritten filter, and re-run the plan to prove the change worked.

This guide is for Vidhyadeep University students doing a Python backend internship. It uses PostgreSQL-style output, because its format is common and readable. MySQL prints different columns but the same ideas apply. You get a node-by-node reading table, three slow-query examples with fixes, a one-page indexing decision tree, and a 14-day practice plan.

What a query plan actually tells you

When you send SQL to a database, the optimizer chooses a strategy: which tables to read first, whether to use an index, and how to join. EXPLAIN shows that strategy as a tree. EXPLAIN ANALYZE also runs the query and adds real timings and row counts. Use plain EXPLAIN on queries that are dangerous to run (large deletes, for instance) and EXPLAIN ANALYZE on safe reads. Wrap writes in a transaction you roll back if you must analyze them.

A plan is read from the innermost, most-indented node outward. Each node has an estimated cost, an estimated row count, and (with ANALYZE) actual time and actual rows. The gap between estimated and actual rows is often the single most useful clue, because it reveals stale statistics or a filter the planner cannot reason about.

The fields worth your attention first

• Node type: Seq Scan, Index Scan, Index Only Scan, Bitmap Heap Scan, Nested Loop, Hash Join, Sort.
• cost=a..b: planner units, not milliseconds. Useful only for comparing plans of the same query.
• rows: estimated rows out of the node. Compare with actual rows.
• actual time=a..b: milliseconds to first row and to last row, per loop.
• loops: how many times the node ran. Multiply actual time by loops for the true cost.
• Rows Removed by Filter: rows read and thrown away. A big number means the access path is wasteful.
• Buffers (with EXPLAIN (ANALYZE, BUFFERS)): how many pages were read from memory versus disk.

Plan-reading cheat sheet: node, meaning, what to do

Slow query 1: a filter that scans the whole orders table

The following is an illustrative scenario with made-up numbers, written to show the shape of real output. It was not run on a live system.

SELECT id, total FROM orders WHERE customer_id = 4821;

Seq Scan on orders  (cost=0.00..18334.00 rows=12 width=16)
                    (actual time=0.031..96.410 rows=14 loops=1)
  Filter: (customer_id = 4821)
  Rows Removed by Filter: 799986
Execution Time: 96.5 ms

Reading it: the database read 800,000 rows to return 14. The estimate (12) was close to actual (14), so statistics are healthy. The problem is purely the access path.

Fix:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Expected new shape: an Index Scan or Bitmap Heap Scan on that index, touching about 14 rows, with execution time dropping to a small fraction of a millisecond range. Always re-run EXPLAIN ANALYZE to confirm rather than assuming.

Slow query 2: an index exists but a function on the column defeats it

-- illustrative
SELECT id FROM users WHERE LOWER(email) = 'asha@example.com';

Seq Scan on users  (cost=0.00..5210.00 rows=50 width=8)
                   (actual time=0.020..38.700 rows=1 loops=1)
  Filter: (lower(email) = 'asha@example.com')
  Rows Removed by Filter: 199999

Reading it: there may already be an index on email, but the query applies LOWER() to every row, so a plain index cannot be used. Notice the estimate (50) versus actual (1): the planner has no good statistics for the expression.

Fix options:

• Create an expression index: CREATE INDEX idx_users_email_lower ON users (LOWER(email));
• Or store emails normalized to lowercase at write time and query the plain column. In a Python app, do this in the model’s save or validation step.

The same trap appears with DATE(created_at) = '2026-11-01', arithmetic on columns, and implicit type casts such as comparing a text column to an integer. Rewrite the predicate so the bare column sits on one side: created_at >= '2026-11-01' AND created_at < '2026-11-02'.

Slow query 3: a join and sort that runs the inner query thousands of times

-- illustrative
SELECT o.id, o.created_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'PENDING'
ORDER BY o.created_at DESC
LIMIT 20;

Limit
  -> Sort  (actual time=210.5..210.5 rows=20 loops=1)
       Sort Method: external merge  Disk: 9120kB
       -> Hash Join (actual time=12.0..160.2 rows=95000 loops=1)
            -> Seq Scan on orders o
                 Filter: (status = 'PENDING')
                 Rows Removed by Filter: 705000
            -> Hash
                 -> Seq Scan on customers c

Reading it: the database found 95,000 pending orders, joined them all, sorted all of them on disk, and then kept 20. The LIMIT came last, so it saved nothing.

Fix: a composite index that matches both the filter and the sort lets the database walk the newest pending orders in order and stop after 20:

CREATE INDEX idx_orders_status_created
  ON orders (status, created_at DESC);

If only a small share of orders are pending, a partial index is smaller and faster: ... ON orders (created_at DESC) WHERE status = 'PENDING';. Also make sure customers.id is a primary key (it normally is), so each of the 20 joins is a quick lookup.

One-page indexing decision tree for a backend intern

Work through these questions in order for any slow query.

1. Is the query actually slow in the real context? If it runs rarely or on a table with a few thousand rows, stop. A Seq Scan is fine.
2. Did you run EXPLAIN ANALYZE on realistic data? If not, load a representative volume first. Plans on ten rows mislead.
   • Estimated rows far from actual? Run ANALYZE table_name; and re-check before adding anything.
3. Where is the most time spent? Fix that node only.
   • Seq Scan with big Rows Removed: go to step 4.
   • Nested Loop with inner Seq Scan: index the join column on the inner table.
   • Sort spilling to disk: go to step 6.
   • Many loops of a small query: that is an application problem (N+1); batch it in Python.
4. Does the filter return a small fraction of the table (roughly under 5 to 10 percent, as a rule of thumb)? If yes, an index helps. If no, the planner will rightly scan; consider narrowing the query instead.
5. Is the column wrapped in a function, cast or arithmetic? Rewrite the predicate, or build an expression index.
6. Is there ORDER BY with LIMIT? Build a composite index: equality columns first, then the sort column in matching direction.
7. Multiple filter columns? Put equality columns before range columns. Order by selectivity only after that.
8. Does the query read only a few columns? Consider a covering index (INCLUDE in PostgreSQL) to get Index Only Scans.
9. Is the table write-heavy? Every index slows INSERT and UPDATE and uses disk. Add one index, measure, and drop unused ones.
10. Re-run EXPLAIN ANALYZE. Record before and after timings. If the plan did not change, the index is not being used; revisit steps 5 to 7.

Reading plans from Python code

You rarely type SQL into a console only. Capture the queries your app really sends. Options include enabling SQL logging in your ORM, using a debug toolbar in development, or turning on the database’s slow-query log. Then paste the exact statement, with realistic parameter values, into EXPLAIN ANALYZE.

Illustrative snippet for running a plan from Python with psycopg2 (shown as a pattern, not as tested code):

with conn.cursor() as cur:
    cur.execute("EXPLAIN (ANALYZE, BUFFERS) SELECT id, total FROM orders WHERE customer_id = %s", (4821,))
    for line in cur.fetchall():
        print(line[0])

Beware that parameterised queries can plan differently from literals. Test with the same form your application uses.

A 14-day plan for learning query plans during your internship

The free two-week online internship from CareerSwami runs 14 days and includes an offer letter and a completion certificate after successful completion. This schedule fits that length and suits any backend track.

Vidhyadeep University internship rules: what to verify before you plan

No official Vidhyadeep University internship or project-work regulation has been verified for this article, so do not assume how an online backend internship will be treated for your programme. One third-party file, hosted on Scribd, is described as the Summer Internship Project manual for MBA students (2025). It reportedly mentions a six-week internship, an independent report, a viva, internal and external evaluation, and a plagiarism limit. Because it is an unofficial upload, treat it only as a hint of what a report-and-viva process can look like, and note that it concerns MBA, not computing programmes.

The practical meaning for you: if your department expects a written report, the performance report from Day 14 is a good starting draft, since it contains measured before-and-after evidence in your own words. If the university expects a longer duration than 14 days, the CareerSwami internship cannot by itself fill it, so find that out early. CareerSwami offers an offer letter and a completion certificate; it does not promise academic credit, so never count on one without written approval.

Also keep your query-plan work original. Any plagiarism limit applies to the report text, so write observations from your own runs rather than pasting plans from tutorials.

Five verification steps for Vidhyadeep students

1. Ask your department office or internship coordinator for the current internship or project-work regulation for your programme and batch, and get the document itself.
2. Email the coordinator: “Is an online internship of 14 days acceptable for credit or for the mandatory requirement, and in which semester?” Keep the reply.
3. Ask which documents are required (offer letter, certificate, report, mentor letter) and the submission format and deadline.
4. Ask whether a viva or presentation is required and how reports are checked for similarity.
5. Check the academic calendar for semester breaks so your 14 days do not clash with exams.

How to prove a fix worked and record it

Evidence matters more than opinion. For every fix, save these five items:

• The exact SQL and parameter values.
• The plan before the change.
• The change itself (index DDL or rewritten query).
• The plan after, with execution time.
• One sentence on the trade-off, such as index size or slower writes.

Run each query a few times and note whether the first run was slower because data was not yet cached. Compare like with like. A fix that only looks fast because of a warm cache is not a fix.

Frequently Asked Questions

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the plan the optimizer intends to use, with estimates only. EXPLAIN ANALYZE executes the query and adds actual times and row counts. Because it really runs the statement, be careful with INSERT, UPDATE and DELETE.

Is a Seq Scan always bad?

No. On small tables, or when a query needs a large share of rows, reading the whole table is often the cheapest plan. It becomes a problem when it discards almost everything it reads, as the large “Rows Removed by Filter” figure shows.

Why does the database ignore my new index?

Common causes: a function or cast on the column, a type mismatch, a filter that matches too many rows, stale statistics, or a composite index whose leading column is not in your filter. Run ANALYZE, then check the decision tree steps on predicates and column order.

Do estimated and actual rows need to match exactly?

No, but a gap of several times or more signals a problem. Refresh statistics first. If the gap remains, look at expressions, correlated columns, or skewed values in the filter.

Can I use these ideas on MySQL or SQLite?

Yes. MySQL offers EXPLAIN and EXPLAIN ANALYZE with different output, and SQLite uses EXPLAIN QUERY PLAN. The concepts of scans, index use, join order and sorting carry over. Check your database’s documentation for exact column meanings.

How many indexes are too many?

There is no fixed number. Each index costs disk space and slows writes. Add indexes for queries you have measured, and remove ones that no plan uses. Your database’s statistics views can show index usage counts.

Can a 14-day internship be enough to learn this?

It is enough to build a solid working habit: read the plan, change one thing, measure again. Mastery of large production systems takes longer, but the 14-day plan above gives you reproducible evidence you can show in an interview or report.

Will my college accept this internship for credit?

That depends on your department’s rules, which you should confirm in writing using the steps above. The CareerSwami internship provides a free offer letter and a completion certificate after successful completion, but no credit is promised.