Using the information schema
The most basic optimization method with column-oriented storage databases is to reduce the number of columns each query returns.
When working with new tables, it is common to select the first 5 or 10 rows. However, a basic select on a wide table may be resource intensive. The information schema provides some column metadata and is a good starting place to learn about your data.
While it does not show as available, views in the information_schema
are always available to query. Feel free to explore the columns
view in the console to explore what information is available before completing the exercise.
This exercise is part of the course
Improving Query Performance in PostgreSQL
Exercise instructions
- Use the
columns
view from theinformation_schema
. - Select the
column_name
,data_type
, andis_nullable
columns. - Use
table_name
to limit results to thedaily_aqi
table.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Examine metadata about daily_aqi
SELECT ___ , ___ , ___
FROM ___
WHERE table_catalog = 'olympics_aqi'
AND ___ = ___ -- Limit to a specific table
;