Improving Query Performance in PostgreSQL
Learn how to structure your PostgreSQL queries to run in a fraction of the time.
Comece O Curso Gratuitamente4 horas15 vídeos53 exercícios10.168 aprendizesDeclaração de Realização
Crie sua conta gratuita
ou
Ao continuar, você aceita nossos Termos de Uso, nossa Política de Privacidade e que seus dados são armazenados nos EUA.Treinar 2 ou mais pessoas?
Tentar DataCamp for BusinessAmado por alunos de milhares de empresas
Descrição do Curso
Losing time on slow queries? Hesitant to share your queries with more seasoned coworkers? In this course, you will learn how to structure your PostgreSQL to run in a fraction of the time. Exploring intertwined data relating Olympic participation, country climate, and gross domestic product, you will experience firsthand how changes in filtering method and using subqueries impact query performance. You will learn the properties of a row oriented database while also seeing how Hawaii's volcanos impact air quality. Restructuring your queries with the query planner and the SQL order of operations, you will soon be dazzling your coworkers with your effortless efficiency.
Treinar 2 ou mais pessoas?
Obtenha acesso à sua equipe à plataforma DataCamp completa, incluindo todos os recursos.Nas seguintes faixas
SQL para administradores de banco de dados
Ir para a trilha- 1
Bringing Together the Data
GratuitoBundle up as you dive into the Winter Olympics! You will learn how to join, subquery, and create temporary tables while finding which Olympic athletes brave sub-freezing temperatures to train. You will also learn about the query planner and how its functionality can guide your SQL structure to faster queries.
All about joins50 xpWhere are all the athletes from100 xpUsing different joins to explore athletes' regions100 xpWhat about the weather100 xpSubqueries and common table expressions (cte)50 xpFiltering to freezing with a subquery100 xpWhere winter is white100 xpCountries with subqueries or CTEs100 xpWorking with temporary tables50 xpCanadians temp table100 xpAnalyze that temp table100 xp - 2
Minimizing Results and Decreasing the Load
Dig up those past algebra memories while learning the SQL order of operations. Find which countries "should" have the most athletes by looking at population and gross domestic product (GDP) while learning the best way to filter. You will also learn when your query aggregates (sums, counts, etc.) and how you can structure your query to optimize this process.
What you write is not what SQL sees50 xpOrder of operations impact on query structure50 xpGroup by and aggregations100 xpCount and count distinct100 xpFiltering in the WHERE clause50 xpOR versus IN with athletes100 xpData type filters100 xpEXPLAIN the filter query plan step100 xpFiltering while joining50 xpWhere to place a region filter100 xpFiltering in the join, where, and select100 xpAggregating with different data granularities50 xpAggregate before joining tables100 xpSouth African trends100 xp - 3
Using Database Designed Properties
Zero in on the properties that improve database performance. Discover when your table is not a table but a view. Learn how your database's storage structure (row or column oriented) impacts your query structure. You will explore volcanic smog while using partitions and indexes to speed your queries.
Queries and tables and views, oh my50 xpData loading and storage50 xpFinding the table type100 xpRow-oriented storage and partitions50 xpRow-oriented storage properties50 xpPreviewing a row-oriented table100 xpPartitioning on location100 xpUsing and creating indexes50 xpFinding the database indexes100 xpCreating and using an index100 xpCompare runtimes100 xpUsing column-oriented storage50 xpColumn-oriented storage properties50 xpUsing the information schema100 xp - 4
Assessing Query Performance
Learn the lingo of the Query Lifecycle and dive into the query planner. Explore how the query planner creates and optimizes the query plan. Find your next vacation locale by looking for countries with recent population growth while also seeing how a join impacts the query steps. Fine tune your optimization techniques by seeing how different filters speed your query times.
Query lifecycle and the planner50 xpExploring pg_tables100 xpBasic EXPLAIN100 xpIndex scans100 xpEXPLAIN the WHERE100 xpA deeper dive into EXPLAIN50 xpEXPLAIN parameters100 xpAggregating and sorting populations100 xpJoining in the query plan100 xpQuery structure and query execution50 xpSubqueries vs. CTEs100 xpWhy the difference?50 xpFiltering impacts100 xpCongratulations50 xp
Treinar 2 ou mais pessoas?
Obtenha acesso à sua equipe à plataforma DataCamp completa, incluindo todos os recursos.Nas seguintes faixas
SQL para administradores de banco de dados
Ir para a trilhacolaboradores
pré-requisitos
Data Manipulation in SQLAmy McCarty
Ver MaisData Analyst
O que os outros alunos têm a dizer?
Junte-se a mais de 15 milhões de alunos e comece Improving Query Performance in PostgreSQL hoje mesmo!
Crie sua conta gratuita
ou
Ao continuar, você aceita nossos Termos de Uso, nossa Política de Privacidade e que seus dados são armazenados nos EUA.