Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        SQL course

        SQL query optimization: plan evidence

        Opening SQL
        Your turn

        SQL query optimization with EXPLAIN

        SQL query optimization starts with a correct answer and a plan you can inspect. Record the query, the input tables and the result before changing anything. A faster query that drops customers or counts a refund twice answers a different question.

        Estimated and measured plans

        EXPLAIN shows the planned operators and estimated row counts. EXPLAIN ANALYZE runs the query and adds execution evidence. In PostgreSQL, a node’s actual rows and time are averages per execution when loops is greater than one. Read those fields together. Planner cost is not elapsed time in milliseconds.

        Follow the inputs through scans, joins and aggregation. Find where the row count grows, then check the join keys against the intended result grain. A large intermediate result can explain why a query does more work than its final row count suggests.

        Rewrites and correctness

        Compare one change at a time. When you need customers with an order, an EXISTS condition expresses that requirement without returning one customer row for every matching order. When you need order totals, aggregate at order grain before joining several detail tables. Keep the treatment of missing matches and NULL values explicit.

        Use the safe rewrite exercises to test those decisions. The refund-report project carries your query through several steps, so you can explain both the result and the change you made.

        Engine boundaries

        The browser workspace uses DuckDB. Its plan operators, optimizer and index behavior differ from PostgreSQL, MySQL and SQL Server. A small browser exercise teaches the diagnosis; its timing is not a production benchmark. Use the target engine and a representative workload to measure a deployment decision.

        Continue with SQL index practice. Database roles, backups, maintenance and server administration are covered in the PostgreSQL administration documentation.

        References: PostgreSQL EXPLAIN, DuckDB EXPLAIN.

        Guides on this topic

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Change your plan

        Checking the price

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.