How To Dev

Introduction In SQL and specifically in PostgreSQL, there are 4 types of Joins as follows: INNER JOIN: Only retrieves records that have a match in both tables. LEFT JOIN: Retrieves all records from the left table, and if there is no match in the right table, the values are set to NULL. RIGHT JOIN: The opposite of LEFT JOIN, rarely used because it can be rewritten in reverse using LEFT JOIN. FULL OUTER JOIN: Retrieves all records from both tables, filling with NULL where there is no match. Nested Loop Join When executing a join, the Postgres Optimizer automatically performs an analysis based on the datasets of the 2 tables to select the most efficient and suitable algorithm for the current situation First, let us look at the Nested Loop Join. This is the most basic join algorithm, and its operational mechanism is very straightforward: it takes each item from one table to compare it with every item from the other table, acting exactly like two nested for loops in programming Use cases Th...

Introduction A Composite Index is an index type that contains two or more columns on the same table. It includes the following characteristics A Composite Index can be created for a maximum of 32 columns This is the default configuration according to the INDEX_MAX_KEYS constant of Postgres In practice, you should not create an index with more than 3 to 4 columns because it increases the index size, slowing down INSERT/UPDATE/DELETE operations The column order is sorted ascendingly by default However, unlike a standard index where you can sort ascending or descending at will, when using a Composite Index, you can only sort the columns all ascending, all descending or in the exact order specified at creation time For example, when using ON table (c1, c2) (default is all ascending), then You can query ORDER BY c1 ASC, c2 ASC You can query ORDER BY c1 DESC, c2 DESC But the index will not work with the query ORDER BY c1 ASC, c2 DESC Operation Order When using a Composite Index, you must ...