Development notes

Thoughts, notes and ideas about development

SQL Query explain plan and Optimization

2016-12-05 SQL Alexey Bogdanov
Query optimization is determination by relational database the most efficient way to execute a given query by considering the possible query plans. Mostly query optimizer is used by databases for optimizing the query in the best possible way. User doesn’t work directly with query optimizer. But some queries can be optimized by exploring its execution plan. Prepare test data Before we dive into queries optimizations and explain plan, let’s prepare a table with test data. Continue reading

Indexes into Relational Databases

A database index is a data structure that improves the speed of data retrieval operations on a database table (increase database performance). But such performance has a cost of additional writes to hard drive and storage space to maintain the index data structure. In nutshell, index is a quick path to a single row into database. Indexes allow us to go directly to he required column without scanning the entire database table. Continue reading

PostgreSQL data types cheat sheet

In this tutorial I will show main PostgreSQL data types such as Boolean, Character, Number, Temporal. Also I will describe when and how we can use them. By the way, Postgres is not limited by the mentioned data types. It also supports such types like JSON, Monetary, Geometric, Network Addresses, etc. The full list of supported data types could be found here: Data Types Boolean Data Type The boolean type (could be shortened to bool) can store only 2 possible values true or false and NULLin case when value is unknown. Continue reading

SQL Data Definition Language for Database in PostgreSQL

As was described into my previous post SQL Commands Types cheat sheet Data Definition Language, or just DDL, is a set of commands for creating, deleting, renaming databases and tables. In this tutorial I will describe how to use DDL with databases(schemes). PostgreSQL will be used for demonstration. How to setup PostgreSQL (and PgAdmin3) on Ubuntu please refer to this tutorial: How to install PostgreSQL on Ubuntu 16.04 CREATE database CREATE DATABASE blog; PostgreSQL will create a database named blog with default parameters. Continue reading

SQL Commands Types cheat sheet

SQL language includes four types of command. In this tutorial I will describe them shortly. Data Definition Language (DDL) DDL is a set of commands for creating, deleting, renaming databases and tables. CREATE Creates a new database or a table. ALTER Modifies the structure of a database or a table. DROP Deletes a database or a table. TRUNCATE Removes all table records, including allocated table spaces. Data Manipulation Language (DML) DML is a set of commands for inserting, deleting, updating and selecting data from the database. Continue reading