Мирон

Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint. A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index.

-- DROP TABLE IF EXISTS public.friends_info;

CREATE TABLE IF NOT EXISTS public.friends_info (
  user_id         bigint
, friend_id       bigint
, unix_time_check bigint
);

ALTER TABLE IF EXISTS public.friends_info
ADD CONSTRAINT friends_info_user_id_friend_id_key UNIQUE (user_id, friend_id);
CREATE INDEX IF NOT EXISTS ix_friends_friend_id ON public.friends_info (friend_id);
CREATE INDEX IF NOT EXISTS ix_friends_user_id ON public.friends_info (user_id);

WITH EncodedData AS (
SELECT gs.rownum
     , gen_random_bytes(4) AS RandBytes
FROM generate_series(1,1000000) gs (rownum)
    WHERE NOT EXISTS (
       SELECT 1
       FROM public.friends_info
       LIMIT 1
  )
), DecodedData AS (
    SELECT rownum
	      ,(get_byte(RandBytes, 0)::bigint + (get_byte(RandBytes, 1)::bigint << 8) + (get_byte(RandBytes, 2)::bigint << 16) + (get_byte(RandBytes, 3)::bigint << 24))::bigint
	            AS RandBigInt
	FROM EncodedData
), PreparedData AS (
 SELECT rownum AS user_id
      , (RandBigInt % 1000000) + 1 AS friend_id
      , RandBigInt AS unix_time_check
 FROM DecodedData
)
INSERT INTO public.friends_info (
  user_id         
, friend_id       
, unix_time_check 
) SELECT 
  user_id         
, friend_id       
, unix_time_check 
FROM PreparedData;

-- EXPLAIN (ANALYZE,BUFFERS)
SELECT * 
FROM friends_info 
LEFT JOIN friends_info i ON friends_info.friend_id = i.user_id 
WHERE friends_info.user_id = 1;

CREATE UNIQUE INDEX IF NOT EXISTS friends_info_user_id_friend_id_key2
ON public.friends_info (user_id, friend_id) INCLUDE (unix_time_check);

-- EXPLAIN (ANALYZE,BUFFERS)
SELECT * 
FROM friends_info 
LEFT JOIN friends_info i ON friends_info.friend_id = i.user_id 
WHERE friends_info.user_id = 1;

-- EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM friends_info 
LEFT JOIN friends_info i
ON friends_info.friend_id = i.user_id 
WHERE friends_info.friend_id=592737;

• распределенных баз
  
• минимизации ресурса памяти и уменьшения или изъятия параллелизма
  

• локальной базы
  
• концентрации ресурсов на выполнении запроса
  

• минимизируются изменения логики выборки данных
  
• часто удается вовлечь индексы в выборку, не обращая внимание на распределение данных внутри домена, когда рост количества таблиц вовлеченных в inner / outer join с различными срезами данных, ведет к отказу оптимизатора запросов использовать индексы
  

MySQL Stored Procedure Programming
By Steven Feuerstein, Guy Harrison
...............................................
Publisher: O'Reilly
Pub Date: March 2006
Print ISBN-10: 0-596-10089-2
Print ISBN-13: 978-0-59-610089-6
Pages: 636

CREATE TRIGGER account_balance_au
 AFTER UPDATE ON account_balance FOR EACH ROW
BEGIN
  DECLARE dummy INT;

  IF NEW.balance<0 THEN
     SET NEW.balance=NULL;
  END IF;

END
$$

select department.*
         , coalesce(dptSlr.minSalary, 0.00) as minSalary
FROM `department` 
left outer join 
(SELECT employee.departmentId, MIN(employee.salary ) AS minSalary 
    FROM employee
    GROUP BY employee.departmentId) dptSlr
on department.id = dptSlr.departmentId;