Introduction to Tuning SQL for Higher Performance

Install Postgres 17.2 on macOS with the EDB interactive installer, and use Pgadmin for querying and managing your database, then configure the postgres password and default port 5432.

Navigate pgAdmin to log in to a PostgreSQL database, explore schemas and tables, and create a test schema and a sample table with id and description.

Learn to create the sales schema and four tables, products, customers, orders, and order items, in Pgadmin, execute the schema and data scripts, and verify row counts for tuning exercises.

Install Postgres and Pgadmin, download and unpack the exercise files, then create the sales schema and tables using the scripts, and insert data to populate the tables.

Examine the hands-on exercise solution by inspecting the sales schema in Pgadmin, counting rows (customers 500, order items 3500, orders 1000, products 100) and reviewing table structures without indexes.

Explore building a database query plan from syntax analysis to semantic checks, cost estimation, and index-driven optimization, including join ordering, plan selection, and runtime tuning.

Explore how PostgreSQL builds query plans using a cost-based optimizer, and how explain reveals details of scan and join nodes and other operations for performance tuning.

Explore how Postgres builds query plans by adjusting parameters, including random_page_cost, work_mem, effective_cache_size, constraint_exclusion, and the Gekko threshold.

Learn how explain and explain analyze reveal Postgres query plans and timing in PgAdmin. Compare select star with columns, observe sequence scans, filtering, and caching effects on planning and execution.

Practice crafting a SQL query to select order items with quantity greater than five in the sales schema, then analyze and explain the query plan for higher performance.

Selects all columns from sales.order_items where quantity exceeds five, illustrating a sequence scan with a filter and discussing execution and planning times.

Explore how to perform aggregation in SQL with sum and group by, apply having filters, and analyze execution plans like hash aggregates and sorts to optimize queries.

select a count of customers grouped by country from the sales customers table, analyze the results, and sort the counts in descending order while explaining insights.

Shows how to count customers by country, group by country, and sort by descending count using explain analyze, hash aggregate, and quicksort, with cost-focused optimization insights.

Explore how inner, left, right, and full joins work, how to use on clauses and aliases, and how query optimization and indexing improve performance by filtering data before joins.

Create and verify indexes on customers, orders, order items, and products. Index by last name, state, country, order date, total amount, customer id, order id, product id, name, and price.

Explore how explain plans decide between index and sequence scans on numeric columns, highlighting bitmap index scans for selective totals and the role of data statistics and histograms.

Analyze how short text indexes affect pattern matching with like 'P%' and 'S%', and use explain plans to decide between sequence and bitmap index scans.

Illustrates how indexing on the customer state Massachusetts enables a bitmap index scan and a hash join when joining orders and customers, with an order total greater than 100.

Query the orders table for rows with total over 100 where customers with last names starting with s are involved, then analyze how a letter pattern filter affects index use.

Demonstrates solving hands-on exercise by joining sales orders with customers on customer_id, using aliases, filtering total amount over 100, and last names starting with S via like 'S%' with index.

Explore inner, left, right, and full joins and how the on clause and aliases improve readability. Learn optimization tips like indexing, filtering before joins, and data volume impact.

Explore how hash join, merge join, and nested loop join work, then examine an inner join between customers and orders with filtering and an explain plan highlighting a hash join.

Explore the three join methods used by Postgres: nested loop joins, hash joins, and merge joins, including when to use each and operator support and indexing on join keys.

Learn how to force hash, merge, and nested loop joins in Postgres with enable options, compare their query plans, and understand when statistics guide the planner.

Practice tuning SQL queries by querying customer and orders to return last names and order dates, sorted by order date ascending, with a forced hash join.

Explore a hands-on solution that queries the customer last name and order date, sorts results by order date ascending, and demonstrates forcing a hash join with set enable hash join.

Explore subqueries in SQL, including single-row, multi-row, correlated, and nested forms, use set operators and CTEs, and prioritize readability in query tuning.

Explore subqueries for basic filtering by selecting customers whose orders exceed the average total, returning first name, last name, and email.

Learn to filter with a subquery that groups by customer_id and uses having sum(total_amount) > 1000 to return customers' names and emails.

Create a query using a subquery to identify customers whose total spending on orders after July 1, 2024 exceeds $1,000.

Identify customers spending over $1,000 on orders after July 1, 2024 by aggregating totals per customer and review the explain plan with hash join, hash aggregate, and sequence scanning.

Rewrite subqueries using the with clause to identify high value customers with orders after 2024-07-01 and spend over 1000. Compare explain costs and readability with subqueries.

Analyze sales region performance using common table expressions to compute order location metrics and customer location counts, then derive region revenue and revenue per customer by shipping state and country.

Create a query using a common table expression to compute the total quantity and total revenue for orders with revenue over $500 from the orders and order items tables.

Create a cte to summarize each order by total items and order total, filter for orders over 500, and sort by date; compare cte, joins, and subqueries using explain plans.

Compare subqueries, joins, and CTEs to decide when to use each for filtering, data combination, and modular query design, balancing readability and performance with explained plans.

Explore how subqueries, ctes, and joins express the same business logic, compare their execution times with explain analyze, and learn to test hypotheses about the fastest query form.

Explore how ai tools transform sql queries by rewriting with cte or joins, explain plans, compare timings, and balance optimization with readability and maintainability.

Apply the like operator for pattern matching on last names, compare sequence scans with a functional index on reverse(last_name) and text normalization, and review explain analyze performance.

Develop a query to fetch product names from the product table that end with ion, and explore adding indexes to improve performance on very large tables.

Build an index on the reversed product name in sales.products and use reverse(name) with like to efficiently find products whose names end in ion.

Explore PostgreSQL text search methods, including ts vector and ts query for full text search, and gin or trigram indexes for efficient querying and fuzzy matching.

Learn full text querying in Postgres by converting document text to a tsvector in english, querying with tsquery, storing a generated tsvector column, and indexing with gin for faster searches.

Explore using trigram-based gin indexes for text search, returning documents with case-insensitive matches and a similarity score to filter and order results by relevance.

Explore how partitioned tables speed queries by dividing large tables into range, list, and hash partitions, with IoT time-series data and geographic or product-based examples.

Create an IoT schema with two vehicle sensor tables, one partitioned by timestamp and one non-partitioned, storing speed, fuel level, engine temperature, tire pressure, and battery voltage.

Compare partitioned and non-partitioned tables with parallel scans and aggregates on IoT vehicle sensor data. See how partitioning can speed simple and complex queries, yet introduce overhead on smaller datasets.

Perform a hands-on exercise to query the minimum and maximum engine temperatures between February 10 and February 17, 2025, across partitioned and non-partitioned vehicle sensor tables.

Compare min and max engine temp using non-partitioned and partitioned tables, filter dates between February 10th and February 17th, and interpret explain analyze results to assess partitioning benefits.

Explore how table, column, and index statistics reveal data distribution and cardinality, guide join order and access method choices, and support maintaining accurate query plans through vacuuming and analysis.

Learn to use postgres statistics to diagnose query performance with pg_stat_all_tables for access patterns and scan types. Explore pg_stat_user_indexes for index efficiency and pg_stat_io_user_tables for io and storage insights.

Analyze schema level performance in the sales schema using pg_stat_all_tables, pg_stat_user_indexes, and pg_stat_io to compare index scans, sequence scans, and IO reads.

Boost query performance by running analyze to update statistics. Reclaim space with vacuum, including regular vacuum, vacuum full for fragmentation, and auto vacuum for maintenance.

Update Postgres statistics and reclaim space with analyze and vacuum analyze on sales.orders and products, and inspect PG stat_user_tables for last analyze and vacuum times.

Examine the Docs schema by viewing the documents table's access and index usage statistics in this hands-on exercise.

Explore table access and index usage stats for the docs.documents table via pg_stat_all_tables and pg_stat_user_indexes, examining sequence vs index scans and gin or id indexes.

Practice designing high performance SQL queries and tuning databases with Postgres or other platforms; build a development environment, generate synthetic data with AI, and study database documentation.