Session 6

Last time

We saw

• an important concept: in a relational database everything (mostly) is a relation
• how the query optimizer works: plan, chooses algorithms, executes
• the cost function the planner tries to minimize : weights * f(CPU + I/0)
• the structure of an explain plan : children and parent nodes
• difference between EXPLAIN and EXPLAIN ANALYZE : cost estimation or execute the query
• how to EXPLAIN ANALYZE update, insert and delete queries with BEGIN … END;
• several types of algorithms for scanning data
  • sequential scans
  • bitmap scans
  • index scans

Precisions on Explain and scans

Let’s go back on a few topics

• Bitmap Heap and Bitmap Index Scans
• Actually show the difference between EXPLAIN and EXPLAIN ANALYZE
• How to update the stats on a table. EXPLAIN ANALYZE does not force the real stats on the planner. Instead, it shows you both the planner’s estimates and what actually happened during execution.
• ANALYZE a table to update the stats

On the menu for today

• indexes
  • how to create them
  • when they are applied
  • B-tree indexes and Hash Indexes
• a small quiz on the algorithms we’ve seen so far https://forms.gle/5fdjpUSr8YaqA9KNA
• practice on a new entirely made up dataset of energy sources using a PL/pgSQL function

EOD

• knowing when to add an index to speed up a slow query
• difference between Hash and B-Tree indexes
• factors impacting the efficiency of an index