Reduce query response time with query tuning

What you should know

From declarative SQL to a procedural execution plan

Scanning tables and indexes

Joining tables

Partitioning data

Challenge Choosing how to partition a table

Solution Choosing how to partition a table

Using PostgreSQL in Codespaces

Explain and analyze

Example plan Selecting with a WHERE clause

Indexes

Challenge Generating a query execution plan

Solution Generating a query execution plan

Indexing

B-tree indexes

B-tree index example plan

Bitmap indexes

Bitmap index example plan

Hash indexes

Hash index example plan

Bloom filter indexes

PostgreSQL-specific indexes

Challenge Choosing an index

Solution Choosing an index

Types of joins

Nested loops

Nested loop example plan

Hash joins

Hash join example plan

Merge joins

Merge join example

Subqueries vs. joins

Solution Designing a join

Horizontal vs. vertical partitioning

Partition by range

Partition by range example

Partition by list

Partition by list example

Partition by hash

Partition by hash example

Challenge Partitioning a table

Solution Partitioning a table

Materialized views

Creating materialized views

Refreshing materialized views

Challenge Creating a materialized view

Solution Creating a materialized view

Collect statistics about data in tables

Analyzing execution statistics with pg stat statements

Reviewing execution plans with the auto explain module

Additional analysis with other pg stats data

Challenge Analyze schema statistics

Solution Analyze schema statistics

Using common table expressions to avoid repetitive computation

Hints to the Query Optimizer

Parallel query execution

Improving cache utilization

Miscellaneous tips

Challenge Design a common table expression

Solution Design a common table expression

Next steps