How can you extract the execution plan of an SQL query in Python and find out its actual execution time?

In Python, to extract the execution plan of an SQL query and estimate its actual execution time, the capabilities of the database management system (DBMS) itself are usually used through EXPLAIN or EXPLAIN ANALYZE.

Example for PostgreSQL using the psycopg2 library:

import psycopg2

conn = psycopg2.connect(dbname="yourdb", user="user", password="pass", host="localhost")
cursor = conn.cursor()

query = "SELECT * FROM your_table WHERE condition"

# Get the execution plan with analysis (includes time)
cursor.execute(f"EXPLAIN ANALYZE {query}")
plan = cursor.fetchall()

for line in plan:
    print(line[0])

cursor.close()
conn.close()

EXPLAIN ANALYZE executes the query and returns a detailed plan with the actual execution time of each step.

For other DBMSs, the syntax may differ, but the idea is the same: use EXPLAIN with the analyze option to get detailed information about the plan and timing.

If an ORM (e.g., Django ORM) is used, you can obtain the SQL query and execute EXPLAIN separately through the cursor.