Queries and tables and views, oh my
Improving Query Performance in PostgreSQL
Amy McCarty
Instructor
Query
SELECT *
FROM ...
- Table
- Base table
- Temporary table
- View
- View
- Materialized view
Base table
| Describe | organized storage |
| Contains | data |
| Loaded | extract, transform, load (ETL) process |
| Source | human resources program, client management system, survey collection, etc. |
Temporary table
| Describe | organized (row and column) storage |
| Contains | data |
| Loaded | query (transient) |
| Source | existing base tables |
CREATE TEMP TABLE my_temptable AS
SELECT *
FROM survey_monkey_results
WHERE survey_date >= '2019-01-01';
SELECT * FROM my_temptable
Standard view
| Describe | stored query |
| Contains | directions / view definition |
| Loaded | never |
| Source | existing base tables |
View utility
- Combine commonly joined tables
- Computed columns
- Summary metrics
- Show partial data in a table
- Show employees but hide salaries
Materialized view
| Describe | stored query | view |
| Contains | data | table |
| Loaded | refresh process | table |
| Source | existing base tables | view |
Materialized view utility
- Same as view
- Faster
Summary of FROM clause references
| What | Why |
|---|---|
| Table | base storage |
| Temp table | speeds query using big table |
| View | complicated logic or calculated fields |
| Materialized view | complicated logic that slows performance |
Information schema
- Provides metadata about database
- Exists in many databases
- PostgreSQL, SQL Server, MySQL
SELECT table_type
FROM information_schema.tables
WHERE table_catalog = 'orders_schema'
AND table_name = 'customer_table'
- BASE TABLE : base table
- LOCAL TEMPORARY : temporary table
- VIEW : view or materialized view
Let's practice!
Improving Query Performance in PostgreSQL
