EXPLAIN ANALYZE Command in PostgreSQL

1. EXPLAIN: shows the query execution plan that PostgreSQL's query planner generates for the specified query. It tells us how PostgreSQL plans to execute the query, including:
◦ What type of scan it will use (sequential scan, index scan)
◦ The estimated cost
◦ The estimated number of rows
2. ANALYZE: actually executes the query and shows the real results, including:
◦ Actual time taken
◦ Actual number of rows processed
eg:

EXPLAIN ANALYZE select * from public.card
where card_number='120000024223'

When we will run this command, we'll see output that looks something like this:

Seq Scan on public.card  (cost=0.00..11.75 rows=1 width=180) (actual time=0.016..0.018 rows=1 loops=1)
  Filter: ((card_number)::text = '120000024223'::text)
Rows Removed by Filter: 100
Planning Time: 0.152 ms
Execution Time: 0.034 ms

Here in this output:
• Seq Scan on public.card:
◦ This indicates PostgreSQL is doing a sequential scan (reading the entire table)
◦ cost=0.00..11.75: unit-less value that represents PostgreSQL's estimate of how expensive the query will be
◦ rows=1: PostgreSQL estimates it will find 1 row
◦ width=180: Estimated average width of each row in bytes
◦ actual time=0.016..0.018: Actual time taken (in ms)
◦ rows=1: Actually found 1 row
◦ loops=1: The operation was performed once
• Filter: ((card_number)::text = '120000024223'::text):
◦ Shows the WHERE clause condition being applied
• Rows Removed by Filter: 100:
◦ 100 rows were checked but didn't match the condition
◦ This means the table had 101 total rows (100 filtered + 1 matching)
• Planning Time: 0.152 ms:
◦ Time taken to generate the query execution plan
• Execution Time: 0.034 ms:
◦ Actual time taken to execute the query
This can be used for:
• Debugging performance issues
• Finding bottlenecks in query performance
#PostgreSQL #query