Home

24: Basic Query Tuning

The Query Processing Pipeline

How do we evaluate what makes a good query and what makes a bad query?

  1. Query is turning into a query tree by the parser.
  2. The rewriting looks at the query tree and modifies it if it thinks it can improve it and also decompose views into underlying table references.
  3. The planner is what we care about. It comes up with plans/strategies on how to get the info.
  4. Finally the plan is executed.

The process that the planner goes through is what the following videos focus on.

EXPLAIN and EXPLAIN ANALYZE

TermDescription
EXPLAINBuild a query plan and display info about it.
EXPLAIN ANALYZEBuild a query plan, run it and display info about it.

These are used for benchmarking + evaluating queries. You will never use them in for fetching real data.

You can also use the explain analyze dropdown option when running a query in PGAdmin.

Solving an Explain Mystery

In the EXPLAIN output: any line that has an arrow -> is known as a query node.

Note that the top line is also technically a query node, although there is no arrow.

A breakdown of one line Hash Join (cost=0.00..1.00 rows=1 width=4) is as follows:

  1. Hash Join is how the node is generating the data.
  2. cost is the amount of processing power required for this step.
  3. rows is a guess at how many rows the step will produce.
  4. width is a guess at the average number of bytes of each row.

How does Postgres make a guess at some of those numbers? It uses the pg_stats table.

You can see this with:

SELECT *
FROM pg_stats
WHERE tablename = 'users'

The data here helps Postgres make an educated guess.

Repository

https://github.com/okeeffed/developer-notes-nextjs/content/postgresql/SQL-And-PostgreSQL-The-Complete-Developers-Guide/24-Basic-Query-Tuning

Sections

The Query Processing Pipeline
EXPLAIN and EXPLAIN ANALYZE
Solving an Explain Mystery

Related

1-Simple-But-Powerful-SQL-Statements
10-Selecting-Distinct-Records
11-Utility-Operators-Keywords-And-Functions
13-PostgreSQL-Complex-Datatypes
14-Database-Side-Validation-And-Constraints
15-Database-Structure-Design-Patterns
16-Building-A-Like-System
17-Building-A-Mention-System
2-Filtering-Records
22-Understanding-The-Internals-Of-PostgreSQL
23-Indexes-For-Performance
24-Basic-Query-Tuning
25-Advanced-Query-Tuning
26-Common-Table-Expressions
27-Recursive-Common-Table-Expressions
28-Simplifying-Queries-With-Views
29-Optimizing-Queries-With-Materialized-Views
3-Working-With-Tables
30-Handling-Concurrency-And-Reversibility-With-Transactions
31-Managing-Database-Design-With-Schema-Migrations
32-Schema-Vs-Data-Migrations
34-Data-Access-Patterns-Repositories
35-Security-Around-PostgreSQL
36-Fast-Parallel-Testing
4-Relating-Records-With-Joins
5-Aggregation-Of-Records
6-Working-With-Large-Datasets
7-Sorting-Records
8-Unions-And-Intersections-With-Sets
9-Assembling-Queries-With-Subqueries
SQL-And-PostgreSQL-The-Complete-Developers-Guide