Want to learn more? Take the full course at learn.datacamp.com/courses/im... at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.
---
Using temporary tables is another option and one that can speed performance. In this lesson, you will learn what they are and how they work.
Temporary, or temp, tables are short-lived storage.
They are created similar to any table, using the CREATE TABLE command with the TEMP qualifier.
They are appealing because they provide transient storage.
They are available for the duration of the database session, meaning they only temporarily tie database resources.
Within your database session, they are available in multiple distinct queries. Contrast this with a CTE or subquery which is only available for that one query.
Temp tables are user specific, meaning they are available only to you as the creator.
Creating a temporary copy of a slow table is a good way to make it faster to query.
This example shows a table of holidays by country. It has many entries with each holiday duplicated by country.
You can create a TEMP table to look at just holidays in one country, here the USA holidays.
Commonly, slow to query tables are the result of a large table, meaning one with many records.
As shown here, the prior slide's world holidays table has about half a million rows. The TEMP table of just the USA holidays has only twenty-five.
Creating the USA TEMP table will be slow because you create it by querying the large world holidays table. However, it is then stored in memory for the duration of the database session. Queries referencing this USA table will subsequently run faster than those referencing the underlying, world holiday table.
Sometimes base table slowness is because the table is actually a view.
Views are similar to tables, with one key difference. Tables contain data. The data is materialized and available. Views contain the directions to data. A view calls an underlying SQL query that runs to populate the results. This is the power of temporary tables. They can materialize a view, bypassing the need to run the underlying SQL query with each reference.
As shown here, the world holidays data is actually a view that is loaded by pulling data from 195 underlying country tables. Referencing it is slow because each reference requires a SQL call back to each of the 195 country tables.
Creating the USA TEMP table materializes the USA data into a table stored in memory for the duration of the database session. Queries referencing this USA table will subsequently run faster.
Referencing the same table multiple times is inefficient and slow. Instead, you can create a temp table of the common, large table. The table is stored in memory and available as a faster copy in subsequent CTEs joined to this one table.
This example creates the same temp table as past slides of USA holidays.
It uses this temp version to join to other information, including origin and celebration date information for religious and secular holidays.
Finally, it is good practice to add the ANALYZE function after the table creation, as seen here in line six. ANALYZE returns no visible output but helps with the query execution plan.
Recall that a SQL query is the order you provide a waiter. The waiter takes the SQL instructions to the cooks or query planner. The planner creates an execution plan, transforming the SQL order into a meal.
ANALYZE calculates the records returned at different query points. It stores this information in the pg_statistics catalog. The query planner uses pg_statistics to estimate the runtime of the possible execution plans. The table statistics improve the query planner's ability to choose the optimal execution plan.
Now it is your turn to practice making some temporary tables.
#SQL #SQLTutorial #DataCamp #Subqueries #table #expressions #Performance #PostgreSQL
24 июл 2024
