What you write is not what SQL sees

Improving Query Performance in PostgreSQL

Amy McCarty

Instructor

Algebraic order of operations

 

  • Lexical (as written)
  • Logical (as executed)

 

PEMDAS BODMAS
Parenthesis Brackets
Exponents Order
Multiplication /Division Division /Multiplication
Addition /Subtraction Addition /Subtraction
Improving Query Performance in PostgreSQL

Applying the order of operations

 

Lexical:

$$ x = 2 + (8 + 4) \div 2 $$

$$ x = 10 + 4) \div 2 $$

$$ x = 14 \div 2 $$

$$ x = 7 $$

 

Logical:

$$ x = 2 + (8 + 4) \div 2 $$

$$ x = 2 + \frac{12}{2} $$

$$ x = 2 + 6 $$

$$ x = 8 $$

Improving Query Performance in PostgreSQL

SQL logical order of operations

Order Clause Purpose
1 FROM Provides directions to the table or tables if the query includes joins
2 WHERE Filters or limits the records
3 GROUP BY Places records into categories
4 SUM(), COUNT(), etc Aggregates
5 SELECT identifies columns to return 
SELECT COUNT(*) FROM tableA WHERE col1 = 77
Improving Query Performance in PostgreSQL

Group by and aggregations

event_location storm elements days
Russia blizzard water 1
Argentina tornado water 1
Argentina tornado wind 1
Australia tornado wind 1
Kuwait haboob wind 2
USA haboob wind 2 
SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements

 

No output - 
your code generated an error

column "storm" must appear in the 
GROUP BY clause or be used in an 
aggregate function
Improving Query Performance in PostgreSQL

Group by and aggregations order of operations

SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements
order SQL clause available columns
1 FROM weather_events all
3 GROUP BY elements elements
4 COUNT elements
Improving Query Performance in PostgreSQL

Group by matches the aggregations

SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements, storm
Improving Query Performance in PostgreSQL

Group by matches the aggregations

SELECT elements, storm, COUNT(*)
FROM weather_events
GROUP BY elements, storm

Results

elements storm count
water blizzard 1
water tornado 1
wind tornado 2
wind haboob 2
Improving Query Performance in PostgreSQL

SQL logical order of operations continued

Order Clause Purpose
...
5 SELECT identifies columns to return
6 DISTINCT removes duplicates
7 ORDER BY arranges results
8 LIMIT removes rows
Improving Query Performance in PostgreSQL

Distinct and limit

row_no location storm elements days
1 Russia blizzard water 1
2 Argentina tornado water 1
3 Argentina tornado wind 1
4 Australia tornado wind 1
5 Kuwait haboob wind 2
6 USA haboob wind 2 
SELECT DISTINCT storm, elements
FROM weather_events
ORDER BY storm LIMIT 3
Improving Query Performance in PostgreSQL
row_no location storm elements days
1 Russia blizzard water 1
2 Argentina tornado water 1
3 Argentina tornado wind 1
4 Australia tornado wind 1
5 Kuwait haboob wind 2
6 USA haboob wind 2 
SELECT DISTINCT storm, elements
FROM weather_events 
ORDER BY storm LIMIT 3
Improving Query Performance in PostgreSQL
row_no location storm elements days
1 Russia blizzard water 1
2 Argentina tornado water 1
3 Argentina tornado wind 1
4 Australia tornado wind 1
5 Kuwait haboob wind 2
6 USA haboob wind 2 
SELECT DISTINCT storm, elements
FROM weather_events 
ORDER BY storm LIMIT 3
order SQL clause available rows
1 FROM weather_events all
Improving Query Performance in PostgreSQL
row_no storm elements
1 blizzard water
2 tornado water
3 tornado wind
5 haboob wind

 

 

SELECT DISTINCT storm, elements
FROM weather_events
ORDER BY storm LIMIT 3
order SQL clause available rows
1 FROM weather_events all
5 SELECT storm, elements all
Improving Query Performance in PostgreSQL
row_no storm elements
1 blizzard water
2 tornado water
3 tornado wind
5 haboob wind

 

 

SELECT DISTINCT storm, elements
FROM weather_events
ORDER BY storm LIMIT 3
order SQL clause available rows
1 FROM weather_events all
5 SELECT storm, elements all
6 DISTINCT 1, 2, 3, 5
Improving Query Performance in PostgreSQL
row_no storm elements
1 blizzard water
5 haboob wind
2 tornado water
3 tornado wind

 

 

SELECT DISTINCT storm, elements
FROM weather_events
ORDER BY storm LIMIT 3
order SQL clause available rows
1 FROM weather_events all
5 SELECT storm, elements all
6 DISTINCT 1, 2, 3, 5
7 ORDER BY storm 1, 5, 2, 3
Improving Query Performance in PostgreSQL
row_no storm elements
1 blizzard water
5 haboob wind
2 tornado water

 

 

 

SELECT DISTINCT storm, elements
FROM weather_events
ORDER BY storm LIMIT 3
order SQL clause available rows
1 FROM weather_events all
5 SELECT storm, elements all
6 DISTINCT 1, 2, 3, 5
7 ORDER BY storm 1, 5, 2, 3
8 LIMIT 3 1, 5, 2
Improving Query Performance in PostgreSQL
Order Clause Purpose Limits
1 FROM provides directions to the table(s)
2 WHERE filters or limits the records # rows
3 GROUP BY places records into categories # columns
4 SUM, COUNT, etc aggregates # rows
5 SELECT identifies columns to return # columns
6 DISTINCT removes duplicates # rows
7 ORDER BY arranges results
8 LIMIT filters records # rows
Improving Query Performance in PostgreSQL

Let's practice!

Improving Query Performance in PostgreSQL

Preparing Video For Download...