How To Dev

Introduction When using GROUP BY to aggregate duplicate data rows, relevant individual data rows are lost. Window Function is used to overcome this limitation. It helps perform aggregation calculations on a set of related rows while preserving the original data rows without collapsing them. Window Function executes after WHERE, GROUP BY and HAVING clauses, but before DISTINCT and ORDER BY in the main statement. Therefore, you cannot place a Window Function directly inside the WHERE clause. If you want to filter data based on the result of a Window Function , you must use a temporary table such as a CTE or Subquery . How to Use To construct a Window Function , combine a Function and a Clause as follows: SELECT FUNCTION () OVER ( PARTITION BY col1 ORDER BY col2 ROWS / RANGE BETWEEN < START > AND < END > ) AS col_name FROM table_name; Function : The function placed before the OVER keyword, such as SUM(), AVG(), ROW_NUMBER(...

Introduction Covering Index is used to enable Index-Only Scan with Non-key Columns , initialized using the INCLUDE clause PostgreSQL supports three types of indexes for this feature: B-Tree (Most common), GiST and SP-GiST All other index types ( GIN, BRIN, Hash ) do NOT support the INCLUDE clause. How it works When you create an index with INCLUDE as follows: CREATE INDEX idx_orders_user_id ON orders (user_id) INCLUDE (total_amount, status) Main column (user_id): Located at the upper nodes and leaf nodes of the B-Tree. Used for searching, filtering ( WHERE ) and sorting ( ORDER BY ). Payload columns (total_amount, status): ONLY stored at Leaf nodes. They do not participate in tree sorting or searching operations. The main purpose is to help Postgres perform an Index-Only Scan , meaning Postgres retrieves data for total_amount and status directly from the index without reading the Heap Page , making the SELECT query execution extremely fast. Advantages Avoiding placing those colu...